topic Dynamic 4 rolling quarter in QlikView

Dynamic 4 rolling quarter

newqlik2017 — Thu, 05 Oct 2017 15:20:28 GMT

I have a Date1 column in this format - YYYYMM.

Date1 = 201703 is 1st quarter

Date1 = 201706 is 2nd quarter

Date1 = 201709 is 3rd quarter

Date1 = 201712 is 4th quarter

Date1 = 201803 is 1st quarter

Date1 = 201806 is 2nd quarter

Date1 = 201809 is 3rd quarter

Date1 = 201812 is 4th quarter

How to create a rolling 4 quarter so that in a pivot table, the 4 quarters (as 4 columns) dynamically get updated?

Re: Dynamic 4 rolling quarter

sunny_talwar — Thu, 05 Oct 2017 15:25:08 GMT

Are you looking to show Max 4 quarters in your pivot table? May be this

If Date1 is in number format.....

{<Date1 = {"$(='>' & (Max(Date1)-100) & '<=' & Max(Date1))"}>}

If Date1 is in Datef format (like you mentioned)

{<Date1 = {"$(='>' & Date(AddYears(Max(Date1), -1), 'YYYYMM') & '<=' & Date(Max(Date1), 'YYYYMM'))"}>}

Re: Dynamic 4 rolling quarter

el_aprendiz111 — Thu, 05 Oct 2017 17:44:52 GMT

Hi,

='Q' & Right(Date1,2)/3

Re: Dynamic 4 rolling quarter

newqlik2017 — Thu, 05 Oct 2017 17:49:21 GMT

Hi Sunny, Yes I'm looking for last 4 quarters. I've the Date as a dimension in my pivot table, not as expression. How does this fit in this case?

Dimension is:
PICK(Dim, Date1, 'DIFF1', 'DIFF2', 'DIFF3')

Expression is:

Pick(Dim,
'$'&Num(round(Sum(COMPANY) * 1.05),'###########,###'),
Num((Sum({<YR_MTH = {"$(=Min(YR_MTH, 2))"}>} COMPANY) - Sum({<YR_MTH = {"$(=Min(YR_MTH, 1))"}>} COMPANY))/100, '#.0%'),
Num((Sum({<YR_MTH = {"$(=Min(YR_MTH, 3))"}>} COMPANY) - Sum({<YR_MTH = {"$(=Min(YR_MTH, 2))"}>} COMPANY))/100, '#.0%'),
Num((Sum({<YR_MTH = {"$(=Min(YR_MTH, 4))"}>} COMPANY) - Sum({<YR_MTH = {"$(=Min(YR_MTH, 3))"}>} COMPANY))/100, '#.0%'))

Re: Dynamic 4 rolling quarter

newqlik2017 — Thu, 05 Oct 2017 17:56:18 GMT

Hi Fer Fer, that gave me 3 quarters from last year but dropped the current quarter. So, for instance if 201703 is the latest date which is 1st quarter 2017, I got Q2, Q3, Q4 for 2016 only and it dropped the 201703 (Q1 for 2017).

Re: Dynamic 4 rolling quarter

newqlik2017 — Thu, 05 Oct 2017 18:03:04 GMT

Never mind, the Q1 didn't get dropped. It moved to the last column, so it wasn't in the same sorting order.

Re: Dynamic 4 rolling quarter

newqlik2017 — Sun, 08 Oct 2017 11:30:47 GMT

https://community.qlik.com/message/1358054#1358054

Hello,

Can you please help for this discussion?

Re: Dimension label - rolling date