Cognos Analytics

 View Only
  • 1.  Gregorian Calendar Customization - Need advise on macros

    Posted Tue October 18, 2022 09:43 AM
    Hi there,

    I'm using 11.2.1 and I have downloaded the updated Gregorian calendar package from https://public.dhe.ibm.com/software/data/sw-library/cognos/mobile/C11/guides/Gregorian_Calendar_Install_Guide.pdf so I could enable dynamic as of date selection. Also I have enabled the Time Perspective selection and added my own selection for 'Prior year end of Dec'. Now that I have a requirement - If user doesn't check this checkbox it needs to check if the data module is related to product X or product Y. If it is product X then as of date should be T-1, else T-2. 

    Therefore in the Gregorian calendar table, I have added a calculated column called 'Usage' that has default 'None'. When the user adds the Gregorian Calendar into their data module, depending on whether it is for product X or Y, they need to modify the calculated column to change it to product X or Y. And then the relative filters that I'm modifying should pick up that information and decide whether as of date is T-1 or T-2.

    I am struggling with adding an IF statement into the default CASE statement, as it is prompting syntax error when I am testing the filters, stating that it is expecting something else rather than an IF statement.


    Here is my code -  The lines highlighted in yellow are the one giving errors

    1. // This is a template expression that is used by the column property 'Lookup reference'.
    2. // To pass validation, the line below must remain as a comment. Do not remove the forward slashes.
    3. // validate: 1 = 1
    4. #
    5. let calTable = $_this.parent.split.ref;
    6. let today = substr($current_timestamp, 0, 10);
    7. let refDate =
    8. case tolower($[Time Perspective] )
    9. when 'prior year end of dec' then
    10. queryValue(calTable + '.PD_TheDate', calTable + ' .TheDate = ' +
    11. queryValue(calTable + '.NM_TheDate', calTable + ' .TheDate = ' +
    12. queryValue(calTable + '.dMonth', calTable + ' .TheDate = ' + _add_months (today, '-' +
    13. queryValue(calTable + '.Month_ ', calTable + ' .TheDate = ' + today)))))
    14. else
      if (queryValue(calTable + ' Usage_ ', calTable + ' .TheDate = ' + today) = 'product X' 
      then (queryValue(calTable + '.TheDate', calTable + ' .TheDate = ' + _add_days (today , '-2')))
      else (queryValue(calTable + '.TheDate', calTable + ' .TheDate = ' + _add_days (today , '-1')))
      end;
    15. let parentId = $_this.parent.idForExpression;
    16. let low = queryValue(calTable + '.dMonth',
    17. calTable + '.TheDate = ' + refDate);
    18. let high = refDate;
    19. parentId + ' >= ' + low + ' AND ' + parentId + ' <= ' + high
    20. #

    p.s. The .Month_ in line 13 is a created column to capture the month no (i.e. Jan will produce 1 and Dec produces 12). The idea of the first case statement is to get 31st Dec of previous year

    ​p.s. Would appreciate for any valuable feedback - This is my first post in this forum so please let me know if there is any thing I should be aware of.

    Thanks.

    ------------------------------
    Wei Bin
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Gregorian Calendar Customization - Need advise on macros

    Posted Wed October 19, 2022 04:42 AM
    Hi Wei Bin,

    there is at least one right parenthesis missing after 'product X' at the end of the if clause in line 14.

    Regards,
    Nils

    ------------------------------
    N D
    ------------------------------



  • 3.  RE: Gregorian Calendar Customization - Need advise on macros

    Posted Wed October 19, 2022 02:35 PM
    Hi Wei Bin,

    In addition to right parenthesis,  if (queryValue(calTable + ' Usage_ ',
    before Usage__ it should have period.

    if (queryValue(calTable + ' .Usage_ ',

    Regards
    Vasu


    ------------------------------
    Vasudeva Cheluri
    ------------------------------



  • 4.  RE: Gregorian Calendar Customization - Need advise on macros

    Posted Wed October 19, 2022 08:46 PM
    Thanks guys.

    I actually have an OR statement after the line 14 IF statement which I didn't include in my original post - the right parenthesis is there.
    I also added the period before usage, but I think the issue is rather it's not accepting IF statement in the CASE statement I have there


    Thanks,
    WB


    ------------------------------
    Wei Bin Fong
    ------------------------------



  • 5.  RE: Gregorian Calendar Customization - Need advise on macros
    Best Answer

    IBM Champion
    Posted Thu October 20, 2022 04:41 AM
    Hi, I would try to turn both the CASE [item] WHEN as well as the IF construction to two CASE WHEN constructions. Once I had a situation when nesting a CASE WHEN construction into a CASE [item] WHEN construction didn't work but nesting two CASE WHEN constructions did work (without obvious reason). It could be similar here.

    ------------------------------
    Philipp Hornung
    ------------------------------



  • 6.  RE: Gregorian Calendar Customization - Need advise on macros

    Posted Thu October 20, 2022 11:21 PM
    Thanks Philipp,

    Managed to construct the inner CASE statement within and it works!


    ------------------------------
    Wei Bin Fong
    ------------------------------



  • 7.  RE: Gregorian Calendar Customization - Need advise on macros

    Posted Thu October 20, 2022 09:28 AM
    Hi Wei Bin,

    If condition parenthesis not closed properly

    if (queryValue(calTable + ' Usage_ ', calTable + ' .TheDate = ' + today) = 'product X' )
    then (queryValue(calTable + '.TheDate', calTable + ' .TheDate = ' + _add_days (today , '-2')))
    else (queryValue(calTable + '.TheDate', calTable + ' .TheDate = ' + _add_days (today , '-1')))


    ------------------------------
    Vasudeva Cheluri
    ------------------------------



  • 8.  RE: Gregorian Calendar Customization - Need advise on macros

    Posted Thu October 20, 2022 11:24 PM
    Code that works - replace the IF statement with nesting CASE statement
    1. // This is a template expression that is used by the column property 'Lookup reference'.
    2. // To pass validation, the line below must remain as a comment. Do not remove the forward slashes.
    3. // validate: 1 = 1
    4. #
    5. let calTable = $_this.parent.split.ref;
    6. let today = substr($current_timestamp, 0, 10);
    7. let refDate =
    8. case tolower($[Time Perspective] )
    9. when 'prior year end of dec' then
    10. queryValue(calTable + '.PD_TheDate', calTable + ' .TheDate = ' +
    11. queryValue(calTable + '.NM_TheDate', calTable + ' .TheDate = ' +
    12. queryValue(calTable + '.dMonth', calTable + ' .TheDate = ' + _add_months (today, '-' +
    13. queryValue(calTable + '.Month_ ', calTable + ' .TheDate = ' + today)))))
    14. else
         case queryValue(calTable + ' Usage_ ', calTable + ' .TheDate = ' + today)
         when 'Product X' then queryValue(calTable + '.TheDate', calTable + ' .TheDate = ' + _add_days (today , '-2'))
         else queryValue(calTable + '.TheDate', calTable + ' .TheDate = ' + _add_days (today , '-1'))
         end;
    15. let parentId = $_this.parent.idForExpression;
    16. let low = queryValue(calTable + '.dMonth',
    17. calTable + '.TheDate = ' + refDate);
    18. let high = refDate;
    19. parentId + ' >= ' + low + ' AND ' + parentId + ' <= ' + high
    20. #


    ------------------------------
    Wei Bin Fong
    ------------------------------