Cognos Analytics

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

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
------------------------------

Original Message:
Sent: Tue October 18, 2022 01:43 AM
From: Wei Bin Fong
Subject: Gregorian Calendar Customization - Need advise on macros

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

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
------------------------------

Original Message:
Sent: Tue October 18, 2022 01:43 AM
From: Wei Bin Fong
Subject: Gregorian Calendar Customization - Need advise on macros

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

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



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

Original Message:
Sent: Tue October 18, 2022 01:43 AM
From: Wei Bin Fong
Subject: Gregorian Calendar Customization - Need advise on macros

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

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
------------------------------

Original Message:
Sent: Wed October 19, 2022 08:45 PM
From: Wei Bin Fong
Subject: Gregorian Calendar Customization - Need advise on macros

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

Original Message:
Sent: Tue October 18, 2022 01:43 AM
From: Wei Bin Fong
Subject: Gregorian Calendar Customization - Need advise on macros

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

Thanks Philipp,

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


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

Original Message:
Sent: Thu October 20, 2022 04:40 AM
From: Philipp Hornung
Subject: Gregorian Calendar Customization - Need advise on macros

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

Original Message:
Sent: Wed October 19, 2022 08:45 PM
From: Wei Bin Fong
Subject: Gregorian Calendar Customization - Need advise on macros

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

Original Message:
Sent: Tue October 18, 2022 01:43 AM
From: Wei Bin Fong
Subject: Gregorian Calendar Customization - Need advise on macros

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

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
------------------------------

Original Message:
Sent: Tue October 18, 2022 01:43 AM
From: Wei Bin Fong
Subject: Gregorian Calendar Customization - Need advise on macros

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

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
------------------------------

Original Message:
Sent: Tue October 18, 2022 01:43 AM
From: Wei Bin Fong
Subject: Gregorian Calendar Customization - Need advise on macros

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