1 Reply Latest reply on Jul 2, 2018 6:09 PM by Shinichiro Murakami

# LOD Calculation 3 month average and current period revenue on same graph

Hi All,

I am having an issue with my lod calculation :

IF [Comparsion Period]="3 Month Average"  THEN

CASE [Choose Dimension]

WHEN "Revenue" Then SUM(IF [Date]=DATEADD('month',-1,[End Date]) THEN {FIXED [Zip Code],[Tier],[Type],[Date]>=DATEADD('month',-3,[Start Date]), [Date]<=DATEADD('month',-1,[End Date]): SUM([Revenue])}/3 ELSEIF [Date]>=[Start Date]and [Date]<=[End Date] THEN {FIXED [Tier], [Type], [Zip Code], [Date]>=[Start Date], [Date]<=[End Date]:SUM(Revenue])}END)

To make things clearer:

I have a sheet that has 6 parameters:

Start Date, End Date, Choose Dimension (revenue, sales, etc), Type of Metric: (Revenue Source), Tier (customer metric), and Comparison Period (3 month average, 12 month average, etc)

The sheet displays two months. I want the 1st month to display the 3 month average of itself and the 2 months prior and I want the month on the left to display the current month's revenue.

Right now I see to be getting numbers much too large that seem like they are a culmination of prior periods or something.

Right now I am unable to provide the workbook.

Thanks for the help.

• ###### 1. Re: LOD Calculation 3 month average and current period revenue on same graph

HI Kelden

Could you please attach sample data as twbx format?

It's way easier to assist you if you could provide the data.

Thanks,

Shin