Business Analytics Connect, learn and share with over 10000 users across the IBM Business Analytics. Join / Log in
I have a scenario where the data is loaded into the data warehouse with a one day lag. So data is loaded for May 30th on May 31st, May 31st data on June 1st, June 1st data on June 2nd. We're using the Gregorian calendar to set the lookup reference and enable relative time for the data module measures. This was working great during testing but now that we're on June 1st, all the Current Month relative time measures show no data. That is correct since the data has a one day lag.
What I would like it to show is the last day of the previous month's data as the Current Month even though we're now on the first day of the current month. Does anyone know how to change the Gregorian calendar to account for this type of situation?
Just to confirm, you want to have the data for yesterday and before that but run the report today while using relative time.If you set the _as_of_date global parameter to use May 31 then that should do what you want.
https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=analysis-customizing-reference-dateYou could use the use yesterday option of the global parameter as well.https://www.ibm.com/docs/en/cognos-analytics/11.1.0?topic=2022-dynamic-as-date-parameter
One enhancement that could be made to the dynamic date technique is to use a report to build the global parameter list.
This report could be included in the deployment that is included within the technique.
The advantages of using a report are
It also demonstrates that a report can be used for building a global parameter, something that people may not be aware of.
Any chance you could get the accelerator catalog updated?
I've forwarded this suggestion to the appropriate party.
This is how macros for relative dates usually look like.You can change second line:
let today = substr($current_timestamp, 0, 10);
"let today = _add_days ( substr($current_timestamp, 0, 10), -1);" in all of them.
That way you would calculate all relative date filters compared to yesterday, and not to today.
let calTable = $_this.parent.split.ref;
let refDate =
case tolower($[Time Perspective] )
when 'use yesterday' then _add_days (today, -1)
when 'use last sunday' then _add_days (today, '-' +
queryValue(calTable + '.DayOfWeek', calTable + ' .TheDate = ' + today))
let parentId = $_this.parent.idForExpression;
let low = queryValue(calTable + '.dWeek',
calTable + '.TheDate = ' +
queryValue(calTable + '.PW_TheDate',
calTable + '.TheDate = ' + refDate)
let high = queryValue(calTable + '.dWeek',
calTable + '.TheDate = ' + refDate);
parentId + ' >= ' + low + ' AND ' + parentId + ' < ' + high
Here is a cheesy way that might work for you: 'clean' the date column in the Data Module.Select the date related field and then in the properties pane, edit the expression.To shift things by one day, change it to: _add_days ( DATE_FIELD, 1) or use -1 to go in the other direction.