4 Replies Latest reply on Jul 3, 2018 1:07 PM by Kelden Smith

    LOD Calculation with COUNT

    Kelden Smith

      Hi, to start I have a measure that is controlled by a parameter called choose dimension as well as one called comparison period. My graph is divided into two months and I want the month on the left to show the 3 month average and the one on the right to show the current month. I got the "sales" and "actual win" to do this fine. My issue comes with the "visits" case because I can't divide a date by 3 to average out the months. I also cannot put the if statement on the outside of the count function because then the if statement contains the dates as well as count and throws the aggregate function error.

       

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

           CASE [Choose Dimension]

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

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

          

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

      END

       

      Thanks for the help.