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
- // This is a template expression that is used by the column property 'Lookup reference'.
- // To pass validation, the line below must remain as a comment. Do not remove the forward slashes.
- // validate: 1 = 1
- #
- let calTable = $_this.parent.split.ref;
- let today = substr($current_timestamp, 0, 10);
- let refDate =
- case tolower($[Time Perspective] )
- when 'prior year end of dec' then
- queryValue(calTable + '.PD_TheDate', calTable + ' .TheDate = ' +
- queryValue(calTable + '.NM_TheDate', calTable + ' .TheDate = ' +
- queryValue(calTable + '.dMonth', calTable + ' .TheDate = ' + _add_months (today, '-' +
- queryValue(calTable + '.Month_ ', calTable + ' .TheDate = ' + today)))))
- 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;
- let parentId = $_this.parent.idForExpression;
- let low = queryValue(calTable + '.dMonth',
- calTable + '.TheDate = ' + refDate);
- let high = refDate;
- parentId + ' >= ' + low + ' AND ' + parentId + ' <= ' + high
- #
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