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

# LOD Calculation with COUNT

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.

• ###### 1. Re: LOD Calculation with COUNT

HI Kelden,

Thanks,

Shin

• ###### 2. Re: LOD Calculation with COUNT

I unfortunately cannot attach the sample data.

• ###### 3. Re: LOD Calculation with COUNT

Use Superstore data to Create Sample

• ###### 4. Re: LOD Calculation with COUNT

Hi Deepak,

Thanks for the reply. I have attached a sample workbook to the original question. The main difference between the two is that the data from each side of the graph should differ from each other. (left one is usually 3x because it encompasses 3 months and I want the average) I want to be able to do an lod calculation to divide only june by 3 and not july. This LOD needs to be in selectdimension. I am specifically referring to # of orders.