IBM Business Analytics

Hello. 
I am trying to run a report that filters on a Start Date and End Date. Ideally, if no start/end date is provided I would calculate a date range. If prompt values are provided, I would use those.

Something like:
[date] is between 
if (?p_Start_Date? is null) then (SYS_DATE() - 4 months) else ( ?p_Start_Date?)
and 
if (?p_End_Date? is null) then (SYS_DATE() - 1 months) else ( ?p_End_Date?)

However,
If I make the prompt optional, and I don't provide prompt values then the filter is not applied.
If I make the prompt required, then the "default" calculated dates aren't used and schedule the report to run once a quarter. 

The idea is to send users a report every three months with the last quarter's data or let them run the report manually for any time period they want.

Maybe my approach is bad?

------------------------------
Gerard Ruppert
------------------------------

Hi Gerard,

I guess you should use relative dates feature.

With the relative dates feature, you can analyze measures filtered by date periods that are relative to a particular date. Examples of relative date filters include current quarter, last quarter, quarter-to-date, or month-to-date.

More information:
Relative date analysis

Best regards,

------------------------------
Patrick Neveu
Positive Thinking Company
------------------------------

Original Message:
Sent: Fri February 11, 2022 01:08 PM
From: Gerard Ruppert
Subject: How to calculate report date range if no prompt value is provided

Hello. 
I am trying to run a report that filters on a Start Date and End Date. Ideally, if no start/end date is provided I would calculate a date range. If prompt values are provided, I would use those.

Something like:
[date] is between 
if (?p_Start_Date? is null) then (SYS_DATE() - 4 months) else ( ?p_Start_Date?)
and 
if (?p_End_Date? is null) then (SYS_DATE() - 1 months) else ( ?p_End_Date?)

However,
If I make the prompt optional, and I don't provide prompt values then the filter is not applied.
If I make the prompt required, then the "default" calculated dates aren't used and schedule the report to run once a quarter. 

The idea is to send users a report every three months with the last quarter's data or let them run the report manually for any time period they want.

Maybe my approach is bad?

------------------------------
Gerard Ruppert
------------------------------

Can you add a radio button before the date to prompt the user for a custom date range or default?  You could then do something like

[Start Date] = 
if(?pCustomDate_Y_N? = 'N')
then(_add_Months(current_date,-4))
else(?p_Start_Date?)

[End Date] = 
if(?pCustomDate_Y_N? = 'N')
then(_add_Months(current_date,-1))
else(?p_End_Date?)

See attached.

------------------------------
Todd Schuman
------------------------------

Original Message:
Sent: Fri February 11, 2022 01:08 PM
From: Gerard Ruppert
Subject: How to calculate report date range if no prompt value is provided

Hello. 
I am trying to run a report that filters on a Start Date and End Date. Ideally, if no start/end date is provided I would calculate a date range. If prompt values are provided, I would use those.

Something like:
[date] is between 
if (?p_Start_Date? is null) then (SYS_DATE() - 4 months) else ( ?p_Start_Date?)
and 
if (?p_End_Date? is null) then (SYS_DATE() - 1 months) else ( ?p_End_Date?)

However,
If I make the prompt optional, and I don't provide prompt values then the filter is not applied.
If I make the prompt required, then the "default" calculated dates aren't used and schedule the report to run once a quarter. 

The idea is to send users a report every three months with the last quarter's data or let them run the report manually for any time period they want.

Maybe my approach is bad?

------------------------------
Gerard Ruppert
------------------------------