Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
newqlik2017
Creator II
Creator II

Dimension label - rolling date

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 20172Q 20163Q 20164Q 20161Q 2017
2Q 20173Q 20164Q 20161Q 20172Q 2017
3Q 20174Q 20161Q 20172Q 20173Q 2017
4Q 20171Q 20172Q 20173Q 20174Q 2017
1 Solution

Accepted Solutions
sunny_talwar

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')

View solution in original post

8 Replies
vishsaggi
Champion III
Champion III

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
MVP
MVP

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

1.png

newqlik2017
Creator II
Creator II
Author

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).

   

NAMETERolling 4 Qtr2Q 20163Q 20164Q 20161Q 20172Q 2016 to 3Q 2016 Change3Q 2016 to 4Q 2016 Change4Q 2016 to 1Q 2017 Change
ABC 11001201200945100.00%0.00%-300%
DEF 300400400210100.00%0.00%-200%

   

lorenzoconforti
Specialist II
Specialist II

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

Untitled.png

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
Creator II
Creator II
Author

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
Specialist II
Specialist II

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
Creator II
Creator II
Author

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

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')