Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Sunny, I had to tweak the script a bit of course but the general idea worked. Duly noted for the future. Much appreciate your support.
Regards,
Vlad
Superb, I am glad it worked after some tweaking