3 Replies Latest reply on Jan 20, 2017 9:00 AM by Yuriy Fal

    How to calculate the difference from previous period over two data sources with a counter value

    Sten Lomme

      Let's say we have a table of printers with the running print counter recorded each month:

       

      DateLocationSerialNumberPrintCount
      30.08.2015New York1234567894 738
      27.10.2015New York1234567894 917
      28.11.2015New York1234567895 214
      31.12.2015New York1234567895 718
      26.02.2016New York1234567896 345
      26.04.2016New York1234567896 628
      27.05.2016New York1234567896 951
      31.07.2016New York1234567897 347
      27.08.2016New York1234567897 537
      26.09.2016New York1234567897 872

       

       

      Then we have a secondary data source listing the sales by month:

       

      LocationMonthYearSales
      New York2201631 588
      New York4201628 654
      New York5201625 846
      New York7201626 844
      New York8201525 345
      New York8201635 155
      New York9201621 035
      New York10201526 788
      New York11201524 232
      New York12201523 405

       

      Since the printer only records the total number of prints since it first started, we have to first use a table calculation to get the total prints for each month using:

      PrintsThisPeriod: ZN(SUM([PrintCount])) - LOOKUP(ZN(SUM([PrintCount])), -1)

       

      Total PrintCount pr Sale is fine using:

      [PrintsThisPeriod]/SUM([Sales].[Sales])

       

      Now we want to calculate how many prints we generated each month for each dollar spent in the same month.

      But how can we do this?

      Year  /  MonthYear  /  MonthYear  /  MonthYear  /  MonthYear  /  MonthYear  /  Month
      201620162016201620162016
      Location245789
      New YorkPrintCount6 3456 6286 9517 3477 5377 872
      New YorkPrintsThisPeriod283323396190335
      New YorkSales31 58828 65425 84626 84435 15521 035
      New YorkPrintCount pr Sale0,009880,012500,014750,005400,01593
      New YorkDifference in PrintsThisPeriod from Previous0,002620,00225-0,009350,01053
      New York%Difference26,52%18%-63,38%195%

       

      Records in orange is what we try to accomplish.

      I have enclosed sheet for reference.