topic Re: Creating 3 month groups from previous month in App Development

Creating 3 month groups from previous month

jay_pee — Wed, 16 Aug 2023 10:42:59 GMT

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?

Re: Creating 3 month groups from previous month

Digvijay_Singh — Wed, 16 Aug 2023 22:32:39 GMT

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.

Re: Creating 3 month groups from previous month

jay_pee — Mon, 21 Aug 2023 10:56:15 GMT

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.

Re: Creating 3 month groups from previous month

Digvijay_Singh — Tue, 22 Aug 2023 03:10:32 GMT

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.

Re: Creating 3 month groups from previous month

jay_pee — Wed, 30 Aug 2023 15:03:20 GMT

Thanks a lot for your help @Digvijay_Singh much appreciated.