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

    Kelden Smith

      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.