Creating 3 month groups from previous month

[jay_pee]

Hello,

I'm looking for a way that I can group data via a date field into 3 month periods in the past starting from the month before the current one.

i.e. if we're in August 2023 then group data as follows...

Group 1 - May 2023, Jun 2023, Jul 2023
Group 2 - Feb 2023, Mar 2023, Apr 2023
Group 3 - Nov 2022, Dec 2022, Jan 2023
Group 4 - etc

and if we're in Dec 2023 then group data as follows...

Group 1 - Sep 2023, Oct 2023, Nov 2023 
Group 2 - Jun 2023, Jul 2023,  Aug 2023
Group 3 - Mar 2023, Apr 2023, May 2023
Group 4 - etc

Is this possible?

[Digvijay_Singh]

Its possible using MonthStart, Monthend functions, something like this - 

If(DateField >= MonthStart(Today(),-3) and DateField <= Monthend(Today(),-1), 'Group 1',

If(DateField >= MonthStart(Today(),-6) and DateField <= Monthend(Today(),-4), 'Group 2',

If(DateField >= MonthStart(Today(),-9) and DateField <= Monthend(Today(),-7), 'Group 3',

If(DateField >= MonthStart(Today(),-12) and DateField <= Monthend(Today(),-8), 'Group 4'))))

I just gave you idea, you may need to correct exact value for 2nd parameter of monthend/monthstart function and in case I missed any parenthesis.

[jay_pee]

Thanks so much for your reply @Digvijay_Singh much appreciated - Would it be possible to do this without having to nest if statements at all? The only reason I'm asking is we may have 3+ years worth of data and I don't want to have to write 12 nested if clauses to set the groups, I was wondering if it's possible for Qlik to count back/assign the data automatically in 3 month groups from the month before today's month? Kind of like using normal quarters but they'd be offset from the current month.

If it needs to be done via nested if statements then I'll have to do that.

[Digvijay_Singh · Accepted solution]

I would rather create quarter field from your date field in the script in your calendar script table, something like this - 

dual('Q' & ceil(month([DateField ]) / 3) & '-' & Year([DateField ]),QuarterStart([DateField ])) AS [Quarter Year]

Indirectly it is grouping your dates in 3 months group and at the same time keeps it format as date and text both.

[jay_pee]

Thanks a lot for your help @Digvijay_Singh  much appreciated.