topic Re: YoY in Pivot Table in QlikView

YoY in Pivot Table

amruta_j — Wed, 25 Nov 2020 16:16:04 GMT

Hi All, need your help again.

I'm trying to incorporate YoY formula in pivot table. Formula is working in straight table. Formula is simple one:

avg({<Flash_Channel={'Online Channel Share'},Month={'$(vCurrMonthYear)'}>}Actual_Flash_Val)

-avg({<Flash_Channel={'Online Channel Share'},Month={'$(vPrevMonthYear)'}>}Actual_Flash_Val)

vCurrMonthYear = 201807

vPrevMonthYear = 201707

I cannot attach my application, but in my pivot table I'm using dimensionality. Below is the formula I'm using in Pivot table:

//YOY//

if(SecondaryDimensionality()=0 and Dimensionality()=4,if(WildMatch(Flash_Channel,'*Share'),

num(sum({<Flash_Channel={'Online Channel Share'},Month={'$(=only(vCurrMonthYear))'}>}Actual_Flash_Val)

-sum({<Flash_Channel={'Online Channel Share'},Month={'$(=only(vPrevMonthYear))'}>}Actual_Flash_Val),'##.## pp'),''))

I tried to debug the formula with no success.

Please let me know if I'm missing something.

Thank You!

Re: YoY in Pivot Table

sunny_talwar — Mon, 30 Jul 2018 11:27:52 GMT

I am not sure what exact expression you have, but why is your set analysis on Month different here

Also, it seems that Date is one of the dimension in your pivot table... is date and year field linked with each other? If they are, then you might need to do this using The As-Of Table or Inter-Record Functions

Re: YoY in Pivot Table

amruta_j — Tue, 31 Jul 2018 02:57:14 GMT

Sorry about the printing mistake. I'm using Month={<'$(vCurrMonthYear)'>} expression.

In my application Month is dimension with the format MonthYear ie 201807.

Basic problem is expression is working in text box or straight table, but not in pivot. Also not sure which function shall I use. Please help me in this.

Thank You!

Re: YoY in Pivot Table

sunny_talwar — Tue, 31 Jul 2018 11:20:04 GMT

Yet again, this doesn't seem right to me Month={<'$(vCurrMonthYear)'>}... are you sure you are using this and if you are... then are you sure that this is even working Amruta?

Re: YoY in Pivot Table

MK9885 — Tue, 31 Jul 2018 11:51:20 GMT

avg({<Flash_Channel={'Online Channel Share'},Year={$(=Max(Year))}>}Actual_Flash_Val)

-avg({<Flash_Channel={'Online Channel Share'},Year={$(=Max(Year)-1)}>}Actual_Flash_Val)

or why not create a Flag in backend for both Current Year and Previous Year?

//In your master calendar use below... ?

if(InYearToDate(YourMasterDate, today(),0), 1, 0) as CurYearFlag,

if(InYear(YourMasterDate, today(),-1), 1, 0) as LastYearFlag,

Can you attach sample XL data and you expected output numbers?

Re: YoY in Pivot Table

amruta_j — Thu, 02 Aug 2018 02:24:41 GMT

This problem has resolved. I've filtered out dimension values to show current year data as a calculated dimension.

When I removed this calculated dimension,Formula worked well.

(avg({<Flash_Channel={'Online Channel Share'},Month1={"$(vCurrMonth)"},Year={"$(vMaxYear)"}>}Actual_Flash_Val)

-avg({<Flash_Channel={'Online Channel Share'},Month1={"$(vCurrMonth)"},Year={"$(vPrevYear)"}>}Actual_Flash_Val))

Thank You all!