This example is when you want to give user ability to select what will be used as a reference date. In the report place a prompt with these values. The name of the parameter is "paramRelTimeDate".
"paramRelTimeDate" is used inside of the relative time macro. Normally, it will take default value which will make that parameter futile. If the above prompt does exist in a report, and if a user selects something from that prompt, then user selection will override the global parameter value.
#
let calTable = $_this.parent.split.ref;
let today = substr($current_timestamp, 0, 10);
Let varLastWorkingDay = queryValue(calTable + '.Previous_workday', calTable + ' .TheDate = ' + today);
Let varUseYesterday = _add_days (today, -1);
Let varUseLastSunday = _add_days (today, '-' +
queryValue(calTable + '.DayOfWeek', calTable + ' .TheDate = ' + today));
Let varAsOfDate = $_as_of_date;
let refDateGlobal =
case tolower($[Time Perspective] )
when 'last working day' then varLastWorkingDay
when 'use yesterday' then varUseYesterday
when 'use last sunday' then varUseLastSunday
else varAsOfDate
end;
Let refDate =
case paramValue( 'paramRelTimeDate', '1900-01-01' )
when "'1900-01-01'" then refDateGlobal
when "'GUI menu'" then refDateGlobal
when "'last working day'" then varLastWorkingDay
when "'use yesterday'" then varUseYesterday
when "'use last Sunday'" then varUseLastSunday
when "'use _as_of_date'" then varAsOfDate
else substr( ParamValue ( 'paramRelTimeDate', '', 'token' ), 1, 10 )
end;
let parentId = $_this.parent.idForExpression;
parentId + ' = ' + refDate;
#
------------------------------
Milan Milovanovic
------------------------------
Original Message:
Sent: Sun September 08, 2024 06:38 AM
From: Jens Bäumler
Subject: calendar relativ date and _as_of_date
Hi Andreas,
With the queryValue function, you can access all elements in your data module.
This allows the filters in the calendar to be adjusted accordingly.
The line where the reference date is determined (usually with _as_of_date) is then related to an element from a table:
let refDate = queryValue('[ORDER_HEADER].[LastOrderDate]');
In my example (using the Cognos Adventure and Leisure data), "LastOrderDate" is the last date in the Order_Header table:
maximum(ORDER_DATE for report)
Here is the result: Current year, previous year, current month, and previous month now refer to the last order date.
Tip for Cognos User Group Germany, Swiss, Austria members: I created the example on our CUG Cognos server.
Changes in Calender Filter definition:
Tip for Cognos User Group Germany, Swiss, Austria members: I created the example on our CUG Cognos server.
------------------------------
Jens Bäumler
Cognos Analytics, Planning Analytics and watsonx
Apparo Group
Germay
www.apparo.de
Original Message:
Sent: Thu September 05, 2024 07:26 AM
From: Andreas Wycisk
Subject: calendar relativ date and _as_of_date
Hi,
if i am not wrong the standard calendar (relativ dates) are based on the global variable _as_of_date.
Is it possible to create a new calendar (relative dates) based on a
calculated date (eg. of a report) or a calculated date within a data module?
Maybe someone can give some hints or provide an example?
Thanks in advance.
BR
Andreas
------------------------------
Andreas Wycisk
------------------------------