This is how macros for relative dates usually look like.
You can change second line:
let today = substr($current_timestamp, 0, 10);
into
"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 today = substr($current_timestamp, 0, 10);
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))
else $_as_of_date
end;
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
#
------------------------------
Milan Milovanovic
------------------------------
Original Message:
Sent: Mon June 05, 2023 09:23 AM
From: IAN HENDERSON
Subject: Using Gregorian Calendar for relative time but need first day of each month to still show last day of prior month as the current month
I've forwarded this suggestion to the appropriate party.
------------------------------
IAN HENDERSON
Original Message:
Sent: Mon June 05, 2023 03:25 AM
From: Marc Reed
Subject: Using Gregorian Calendar for relative time but need first day of each month to still show last day of prior month as the current month
Ian,
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
- No scope for errors when someone tries to replicate the parameter list.
As it is someone need to enter the parameter values exactly as they are for the technique to work. - It would replace the current techniques multiselect parameter lists with a single drop down.
With the current technique a user can choose multiple time perspectives.
By using a report this would no longer be the case. It removes user training and user error.
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?
------------------------------
Marc Reed
Original Message:
Sent: Thu June 01, 2023 01:50 PM
From: IAN HENDERSON
Subject: Using Gregorian Calendar for relative time but need first day of each month to still show last day of prior month as the current month
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=date-setting-as-global-parameter
https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=analysis-customizing-reference-date
You 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
------------------------------
IAN HENDERSON
Original Message:
Sent: Thu June 01, 2023 10:54 AM
From: Dong Banh
Subject: Using Gregorian Calendar for relative time but need first day of each month to still show last day of prior month as the current month
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?
Thank you.