Pivot Table YTD, PYTD and Seasonality Calculation Question

[basildur]

Hello Dear Experts,

I have came across a problem that I can't wrap my head around. I have the below Pivot Table with Targets for 2019, however currently it compares YTD vs full previous year. Here is what I Would like to do:

In Total's 1st line is fine,

in the 2nd line i would like to have 95+85=180

and in the 3rd line I would like to have 9+2=11

Now to the messy part - set analysis. These are my current calculations for aforementioned 4 measures:

Volume : SUM({$<DateNum={">=$(=Num(YearStart(Max(DateNum)))) <=$(=Num(MonthEnd(Today())))"}>} TOTAL <Month,EHO1> Containers)

Seasonality Target Volume: 

(SUM({$<DateNum={">=$(=Num(YearStart(Max(DateNum)))-365) <=$(=Num(MonthEnd(Today()))-365)"}>} TOTAL <EHO1> Containers ) * vTarget) *
SUM({<Year={">=$(=year(today())-3) <=$(=year(today())-1) "}>}TOTAL <Month,EHO1> Containers) /
SUM({<Year={">=$(=year(today())-3) <=$(=year(today())-1) "}>}TOTAL <EHO1> Containers) 

//vTarget is basically just 1.053. The part with -3 years and -1 year is basically where we calculate seasonality.

Seasonality Target Variance: 

SUM({$<DateNum={">=$(=Num(YearStart(Max(DateNum)))) <=$(=Num(MonthEnd(Today())))"}>} TOTAL <Month,EHO1> Containers ) -

(SUM({$<DateNum={">=$(=Num(YearStart(Max(DateNum)))-365) <=$(=Num(MonthEnd(Today()))-365)"}>} TOTAL <EHO1> Containers ) * vTarget) *
SUM({<Year={">=$(=year(today())-3) <=$(=year(today())-1) "}>}TOTAL <Month,EHO1> Containers) /
SUM({<Year={">=$(=year(today())-3) <=$(=year(today())-1) "}>}TOTAL <EHO1> Containers) 

Can anybody help me figure out a way to just show YTD vs YTD-1 ? Rather than YTD vs Total Y-1

[sunny_talwar]

---

@basildur wrote:

in the 2nd line i would like to have 95+85=180

and in the 3rd line I would like to have 9+2=11

for all columns or future columns?

[basildur]

@sunny_talwar Total 180, Jan 95, Feb 85, Mar 0, Apr 0 etc. When we will be in March then March will change from 0 to whatever value it will be and so on.

Similar to the 3 Total 11, Jan 9, Feb 2, Mar 0, Apr 0 etc. When we will be in March then March will change from 0 to whatever value it will be and so on.

[sunny_talwar]

May be just do this

If(
Sum({$<DateNum={">=$(=Num(YearStart(Max(DateNum)))) <=$(=Num(MonthEnd(Today())))"}>} TOTAL <Month,EHO1> Containers) <> 0, 
YourExpressions, 0)

[basildur]

@sunny_talwar This is closer, but the Total is still for the whole line:

[sunny_talwar · Accepted solution]

Just do this

Sum(Aggr(
If(
Sum({$<DateNum={">=$(=Num(YearStart(Max(DateNum)))) <=$(=Num(MonthEnd(Today())))"}>} TOTAL <Month,EHO1> Containers) <> 0, 
YourExpressions, 0),
YourChartDimensionsHere))

 Make sure to replace YourChartDimensionsHere with the dimensions you are using in your table

[basildur]

@sunny_talwar Thanks, works like a charm. I guess I can also use the same expression in a KPI
Box on the Main sheet and to calculate the % delta.

[sunny_talwar]

Yes you can

[basildur]

@sunny_talwar 

Hi Sunny, while we are still on the topic, would you be able to point in the right direction here. The calculation for the delta works fine, but I am trying now to do the same but with %, however the result doesn't make a lot of sense:

Sum(Aggr(
If(
Sum({$<DateNum={">=$(=Num(YearStart(Max(DateNum)))) <=$(=Num(MonthEnd(Today())))"}>} TOTAL <Month,EHO1> Containers) <> 0,

//Below the expression to get the % delta

(Sum({$<DateNum={">=$(=Num(YearStart(Max(DateNum)))) <=$(=Num(MonthEnd(Today())))"}>} TOTAL <Month,EHO1> Containers)
/
((Sum({$<Year={$(=year(today())-1)}>} TOTAL <EHO1> Containers ) * vTarget) *
SUM({$<Year={">=$(=year(today())-3) <=$(=year(today())-1) "}>}TOTAL <Month,EHO1> Containers) /
SUM({$<Year={">=$(=year(today())-3) <=$(=year(today())-1) "}>}TOTAL <EHO1> Containers))
-1),

//Expression Ends

0),
Month,EHO1))

Total should 191/180-1=6.11%, Jan 104/95-1=9.47% and Feb 87/85-1= 2.35%.

Here (in the app) I get slightly different result. Plus it seems that 11.52% = 9.66% + 1.86%

Any thoughts ?

[sunny_talwar]

 Try this

Sum(Aggr(
If(
Sum({$<DateNum={">=$(=Num(YearStart(Max(DateNum)))) <=$(=Num(MonthEnd(Today())))"}>} TOTAL <Month,EHO1> Containers) <> 0,

//Below the expression to get the % delta

(Sum({$<DateNum={">=$(=Num(YearStart(Max(DateNum)))) <=$(=Num(MonthEnd(Today())))"}>} TOTAL <Month,EHO1> Containers), 0)
,
Month,EHO1))
/
Sum(Aggr(
If(
Sum({$<DateNum={">=$(=Num(YearStart(Max(DateNum)))) <=$(=Num(MonthEnd(Today())))"}>} TOTAL <Month,EHO1> Containers) <> 0,
((Sum({$<Year={$(=year(today())-1)}>} TOTAL <EHO1> Containers ) * vTarget) *
SUM({$<Year={">=$(=year(today())-3) <=$(=year(today())-1) "}>}TOTAL <Month,EHO1> Containers) /
SUM({$<Year={">=$(=year(today())-3) <=$(=year(today())-1) "}>}TOTAL <EHO1> Containers))
-1),

//Expression Ends

0),
Month,EHO1))

So, instead of a single Sum(Aggr(....))... you need two... one for the numerator and one for the denominator

Best,

Sunny