YoY in Pivot Table

[amruta_j]

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!

[sunny_talwar]

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

[amruta_j]

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!

[sunny_talwar]

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]

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?

[amruta_j · Accepted solution]

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!