3 Replies Latest reply on Apr 10, 2017 10:19 AM by Robin Fay

# Summarizing AGG calcuation by Year

I am having trouble summarizing a calculation by year

I am trying to show bias of a forecast over time using the following equation.

(ZN(SUM([Actual]))-ZN(SUM([FC])))/ABS(ZN(SUM([Actual]))-ZN(SUM([FC])))

if Forecast is higher than actual the result will be -1

if Actual is higher than Forecast the result will be 1

if  Actual is equal Forecast than the result will be 0

When I sure the Row Grand Total I get the correct answer,(if I choose "sum" from the "total using" under Analysis>totals)

What I want to do is remove the Month from the view and summarize by year (so i can select customer month ranges), However when I do that The results are all wrong.

any ideas to show the summarized monthly bias by Year?

LOD maybe?

• ###### 1. Re: Summarizing AGG calcuation by Year

Hey Robin!

LOD is definitely one option. I used the following LOD calc and it worked:

{FIXED datetrunc('month',[Date]), [Category] : (ZN(SUM([Actual]))-ZN(SUM([FC])))/ABS(ZN(SUM([Actual]))-ZN(SUM([FC])))}

It fixes the result across each month/category. Then, the yearly view sums across each month. Result:

Hope this helps

Michael

• ###### 2. Re: Summarizing AGG calcuation by Year

Hi Robin,

It is Easy using LOD and when I used your bias Calculation in LOD, I get This:

I used this LOD:

SUM({FIXED [Category], [Date (Months)]:([bias])})

Hope it Helps!!! See  attached.

Thanks

Deepak

• ###### 3. Re: Summarizing AGG calcuation by Year

Works Great!

thanks for the help!