2 Replies Latest reply on Sep 23, 2018 10:54 PM by Gavin Wong

Calculate between 2 LODs

HI community

i am trying to calculate the LODs between current years average sales to last year's  average sales.

However i tried to create a calculated field to just have 2017's data... i simply couldnt do it.

eg. 2017's average sales for customer segment vs 2018 average sales per customer segment.

Need them in 2 separate calculated fields as they will need to be used elsewhere.

My objective is to use the average sales per order (total sales / unique order id) in 2018 for each segment, compare it against the entire 2017 average sales at the year level as a %. (ignoring segments in 2017)

any thoughts ?

• 1. Re: Calculate between 2 LODs

Hi Gavin,

Is this what you are after?

I have created two calculated fields: 2017 Average Sales and 2018 Average Sales By Segment based on how I understood the requirements you gave.

2017 Average Sales = {FIXED DATEPART('year', [Order Date]): SUM(IF (DATEPART('year', [Order Date])) = 2017 THEN [Sales] END) / COUNTD(IF (DATEPART('year', [Order Date])) = 2017 THEN [Order ID] END) }

2018 Average Sales By Segment = {FIXED DATEPART('year', [Order Date]), [Segment]: SUM(IF (DATEPART('year', [Order Date])) = 2018 THEN [Sales] END) / COUNTD(IF (DATEPART('year', [Order Date])) = 2018 THEN [Order ID] END) }

Find attached a workbook with the solution.

Regards,

Emmanuel.

1 of 1 people found this helpful
• 2. Re: Calculate between 2 LODs

Emmanuel Ikehi

thanks for your solution. I am almost there at my desired outcome. I have attached the screenshot and workbook.

in the screen shot, i need another calculated field to calculate the red square divide by the value in purple square

ie

1,165 / 1,910

1,004 / 1,910

801 / 1,910

Attached is the work book in the tab "Help"