How to create 'Reporting Period' which consists of current month and 2 previous months?

[Zaqwer]

Hello,

How would you deal with a scenario where you need to create 'Reporting Period' filter button which would includes current month as well as 2 previous months? I have date in YYYYMMDD format.

Thank you for suggestions.

[MartW]

I would go for the easiest option that would be to create an indicator.

i assume that you got a calendar within your datamodel. if so you can simple add a indicator with an if-statement. then in the front end you can use this indicator within a bookmark and link this bookmark to a button.

this will make sure that you indicator (when reloading the data of course) is up to date with this month and the last 2 months.

[Dalton_Ruer]

I would start out with a variable in your Load Script like this:

Let v2MonthsAgo = MonthStart(AddMonths(Today(), -2));

It creates a variable that as of today would be '9/1/2022' (or whatever your default date format is. 

Then while loading your table of values simply compare your date to the variable value. Notice that I wrap my dollar sign expansion of the variable with single quotes. So that it isn't comparig it to 9 divided by 1 divided by 2022. 

If (DateValue >= '$(v2MonthsAgo)', 'Yes', 'No') as InRolling3MonthCalculation

You can use that format for anything you would like like rolling 12 months, previous quarter whatever. 

Once you understand that code then I'd suggest you kick things up a notch using the Dual datatype. 

If (DateValue >= '$(v2MonthsAgo)', Dual('Yes', 1), Dual('No', 0)) as InRolling3MonthCalculation

Why? Great question. Because the Dual datatype allows you to still display Yes / No value for the end user to choose from in a filter, or display in a table, but it also allows you to do some super cool math. Yes ... MATH.

Notice below I have a filter for the InRolling3MonthCalculation so user can choose "Yes" to filter only to the rows of data for the rolling 3 months. But notice below my simply Sum(Value) KPI, I also have another KPI where I'm saying give me a Sum of the Value TIMES the InRolling3MonthCalculation field. Because MATH tells us that any value times 1 is the value and any value times 0 is 0. Thus I can easily display a sum for the rolling 3 months without making the user filter anything. 

If you are thinking "HOLY SMOKES I've been using IF expressions everywhere and IF expressions are slow, especially with TEXT, but math is super fast" then you might just be having an AHA MOMENT. Below is the simple code I created for this exercise in case you want to copy/paste it and play some games. Not knowing where your date/text comes from, I wanted to be sure I demonstrated how to convert a pure text value to a Qlik "DATE" value. 

Let v2MonthsAgo = MonthStart(AddMonths(Today(), -2));

DataTable:
LOAD Key, Date(Date#(DateValue, 'YYYYMMDD'), 'MM/DD/YYYY') as DateValue, Value;
LOAD * Inline [
Key, DateValue, Value
1, '20221101', 100
2, '20220913', 150
3, '20220118', 200
4, '20220920', 160
5, '20220819', 100
];

Join Load
Key,
If (DateValue >= '$(v2MonthsAgo)', Dual('Yes', 1), Dual('No', 0)) as InRolling3MonthCalculation
Resident DataTable;

[vinieme12]

Create a button with below Action

Action: Toggle Field Selections

Field : Yourdatefield

Fx : 

= '('&concat({<Yourdatefield={">=$(=Date(Monthstart(Max(Yourdatefield),-2)))<=$(=Date(Max(Yourdatefield)))"}>}distinct chr(34)&YourYearMonthField&chr(34),'|')&')'