Rolling 90 Day calculation in rolling 12 month bar chart

[orangebloss]

I need to include a rolling 90 day calculation in a bar chart as below and struggling somewhat with the logic.

Every record has an eventdate (Existing field).

A record should be counted if the eventdate is in the 90 days before the reportdate (doesn't exist) 

ReportDates to form X axis are a rolling 12 month period calculated from the current month:

month1 = year(monthstart(addmonths(today(),-1)) )& num(month(monthstart(addmonths(today(),-1)) ))

month2 = year(monthstart(addmonths(today(),-2)) )& num(month(monthstart(addmonths(today(),-2)) ))

month3=year(monthstart(addmonths(today(),-3)) )& num(month(monthstart(addmonths(today(),-3)) ))

month4=year(monthstart(addmonths(today(),-4)) )& num(month(monthstart(addmonths(today(),-4)) ))

Etc

As this doesn't exist do I need to create a new table in the loadscript to create this as a reference table?

current month August 2024:

Record num	EventDate	month1 (calculated as July 2024)	month2 (June 2024)	month3 (May 2024)	month4 (April 2024)	month5 (March 2024)	month6 (Feb 2024)	month7 (Jan 2024)	month8 (Dec 2023)	month9 (Nov 2023)	month10 (Oct 2023)
1	31/01/2024					Y	Y	Y
2	12/04/2024		Y	Y	Y
3	11/11/2024							Y	Y	Y
4	19/06/2024	Y	Y

When we move to next month the calcs would look like this:

Record num	EventDate	month1 (calculated as August 2024)	month2 (July 2024)	month3 (June 2024)	month4 (May 2024)	month5 (April 2024)	month6 (March 2024)	month7 (Feb 2024)	month8 (Jan 2024)	month9 (Dec 2023)	month10 (Nov 2023)
1	31/01/2024						Y	Y	Y
2	12/04/2024			Y	Y	Y
3	11/11/2024								Y	Y	Y
4	19/06/2024	Y	Y	Y

[Kushal_Chawda]

@orangebloss  Both of the rolling values are at different granularity. What will be the base date for report month to look at rolling 90 days? e.g for Apr 2024,  base date will be 30-Apr-2024 to consider it rolling 90 days to 31/01/2024, so 31/01/2024 to 30-Apr-2024?

[orangebloss]

The Eventdate minus 90 days will determine which report months the value will be counted in.

So if the event date is 10th April the 90 days starts from January 10th and so it would appear in Jan24, Feb24, March 24 and April 24 . I'm tempted to just change it to a rolling 3 month period if that will make it easier but the user has specified 90 days..... I'm feeling like this is impossible with only one entry per event....

[Digvijay_Singh]

Probably HIC's as-of-table method might help in this situation, not sure though - 

https://community.qlik.com/t5/Design/The-As-Of-Table/ba-p/1466130

[orangebloss]

Thanks I will give that a go!