Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Partner
Partner

YoY in Pivot Table

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!


1 Solution

Accepted Solutions
Partner
Partner

Re: YoY in Pivot Table

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!

View solution in original post

5 Replies

Re: YoY in Pivot Table

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

Capture.PNG

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

Partner
Partner

Re: YoY in Pivot Table

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

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?

MK9885
Honored Contributor II

Re: YoY in Pivot Table

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?

Partner
Partner

Re: YoY in Pivot Table

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!

View solution in original post