Dimension label - rolling date

[newqlik2017]

I have a field Qtr - which holds 4 values (rolling 4 quarter year). I need to display the rolling 4 quarter year as column headers (dimension labels). I've this field as a dimension in my pivot table. How to get correct result as below?

Qtr
1Q 2017		2Q 2016	3Q 2016	4Q 2016	1Q 2017
2Q 2017		3Q 2016	4Q 2016	1Q 2017	2Q 2017
3Q 2017		4Q 2016	1Q 2017	2Q 2017	3Q 2017
4Q 2017		1Q 2017	2Q 2017	3Q 2017	4Q 2017

[vishsaggi]

Can you explain a little more and share some sample data or a file to work on? You can drag your quarter field to top of the expression that way the quarters will become headers, is this what you are looking for?

[maxgro]

I'm not sure if you want the labels (bottom chart) or the values (top chart)

.qvw with test data and charts in the attachments

[newqlik2017]

Here's what am looking for:

From the first table below, I was able to change the Header for rolling 4 quarters dynamically (green highlight)

201603 - 2Q 2016

201606 - 3Q 2016

201609 - 4Q 2016

201703 - 1Q 2017

I used this in the load script to achieve it:

RIGHT(YR_YR, 2)/3 & 'Q ' & LEFT(YR_MTH,4) AS ROLLING_4_QTR_YR

Now, I need to convert the last 3 field labels (which are hard coded value in the qlikview table above) to the right 3 columns highlighted yellow ( these quarter year value should be dynamic).

NAME	TE	Rolling 4 Qtr	2Q 2016	3Q 2016	4Q 2016	1Q 2017	2Q 2016 to 3Q 2016 Change	3Q 2016 to 4Q 2016 Change	4Q 2016 to 1Q 2017 Change
ABC			1100	120	1200	945	100.00%	0.00%	-300%
DEF			300	400	400	210	100.00%	0.00%	-200%

[lorenzoconforti]

Hi

Do you necessarily need the columns laid out in that way?

If not, the attached solves your problem just shown in a slightly different way

Basically I have modified slightly the load script and added a table with reference to the quarter

Then amended your pivot table and added an extra line which shows the % difference with the previous quarter

Hopefully it helps

Lorenzo

[newqlik2017]

Hello All, thank you for your inputs.

I do need the table in the specified format as shown below. These are the column headers (labels). However, the quarter number and year (2Q, 3Q, 4Q, 1Q, 2016, 2017) are values that should be dynamically updated as change with quarter. I got the green part working (which is dimension) but the yellow part (which is expression in the pivot chart) doesn't work. It's hard coded right now, and this has to be dynamic instead of hard coded.

Help! please.

[lorenzoconforti]

Do you always only have 4 periods you want to analyse? Will the table go from "3Q 2016" to "2Q 2017" (4 periods) or will it go "2Q 2016" to "2Q 2017" (5 periods)?

[newqlik2017]

I always have 4 quarters to analyze. There will never be more than 4 quarters.

So, currently it is     2Q 2016, 3Q 2016, 4Q 2016, 1Q 2017

Next quarter, it'll be 3Q 2016, 4Q 2016, 1Q 2017, 2Q 2017 and

next one would be   4Q 2016, 1Q 2017, 2Q 2017, 3Q 2017

[sunny_talwar · Accepted solution]

Try this as your calculated dimension

=Pick(Dim, YR_MTH,

Ceil(Month(Max(TOTAL Date#(YR_MTH, 'YYYYMM'), 4))/3) & 'Q' & ' ' & Year(Max(TOTAL Date#(YR_MTH, 'YYYYMM'), 4)) & ' to ' & Ceil(Month(Max(TOTAL Date#(YR_MTH, 'YYYYMM'), 3))/3) & 'Q' & ' ' & Year(Max(TOTAL Date#(YR_MTH, 'YYYYMM'), 3)) & ' Change',

Ceil(Month(Max(TOTAL Date#(YR_MTH, 'YYYYMM'), 3))/3) & 'Q' & ' ' & Year(Max(TOTAL Date#(YR_MTH, 'YYYYMM'), 3)) & ' to ' & Ceil(Month(Max(TOTAL Date#(YR_MTH, 'YYYYMM'), 2))/3) & 'Q' & ' ' & Year(Max(TOTAL Date#(YR_MTH, 'YYYYMM'), 2)) & ' Change',

Ceil(Month(Max(TOTAL Date#(YR_MTH, 'YYYYMM'), 2))/3) & 'Q' & ' ' & Year(Max(TOTAL Date#(YR_MTH, 'YYYYMM'), 2)) & ' to ' & Ceil(Month(Max(TOTAL Date#(YR_MTH, 'YYYYMM'), 1))/3) & 'Q' & ' ' & Year(Max(TOTAL Date#(YR_MTH, 'YYYYMM'), 1)) & ' Change')