Grouping months in a different way.

[christian77]

Hi Qlikers.

I have a fun little challenge here.

I need to present some data in bar charts and tables evolving in time.

Data has to be grouped by Quarter and Month in the next way. Groups of 3 months that have passed are grouped in quarters. Months not completing a quarter go separated.

Months	1	2	3	4	5	6	7	8	9
Quarters	q1	q1	q1	q2	q2	q2	q3	q3	q3
Time Period	Q1			Q2			M7	M8	M9
Months	1	2	3	4	5	6	7	8	9	10
Quarters	q1	q1	q1	q2	q2	q2	q3	q3	q3
Time Period	Q1			Q2			Q3			M10
Months	1	2
Quarters	Q1	Q1
Time Period	M1	M2

1^st. I’m taking last month closed. Today, that would be September.

2^nd To show a grouped quarter, I need one more month. See the example.

I can do this in the script grouping, but I’d prefer to create a table with the field Month to link to my field Month and then group those months with a new field renamed Time Period.

The thing is that the ‘mask’ is moving throughout the year. It’s not fixed. Previous years go the same way in order to compare.

I’ve already calculated some dates that we’ll need.

////////////FUNCTIONAL DATE   ---Last date closed monthLET vL.FunctionalDate = date(monthstart(Today())-1,'YYYYMM');trace $(vL.FunctionalDate);
////////////FINISHED QUARTERS   ---Finished quarters (does it work with month =1. Need to review.LET vL.FinishedQuarters = floor((month(vL.FunctionalDate)-1)/3);trace $(vL.FinishedQuarters);
////////////LAST MONTH LAST QUARTER --- Until here we group in quarters. From this point in months.LET vL.LastMonthQuarter = $(vL.FinishedQuarters)*3;trace $(vL.LastMonthQuarter);

I hope it is well stated. If they are some questions, I’ll explain it more. Suggestions?

Thank you Qlikers.