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

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

 Date Location SerialNumber PrintCount 30.08.2015 New York 123456789 4 738 27.10.2015 New York 123456789 4 917 28.11.2015 New York 123456789 5 214 31.12.2015 New York 123456789 5 718 26.02.2016 New York 123456789 6 345 26.04.2016 New York 123456789 6 628 27.05.2016 New York 123456789 6 951 31.07.2016 New York 123456789 7 347 27.08.2016 New York 123456789 7 537 26.09.2016 New York 123456789 7 872

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

 Location Month Year Sales New York 2 2016 31 588 New York 4 2016 28 654 New York 5 2016 25 846 New York 7 2016 26 844 New York 8 2015 25 345 New York 8 2016 35 155 New York 9 2016 21 035 New York 10 2015 26 788 New York 11 2015 24 232 New York 12 2015 23 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  /  Month Year  /  Month Year  /  Month Year  /  Month Year  /  Month Year  /  Month 2016 2016 2016 2016 2016 2016 Location 2 4 5 7 8 9 New York PrintCount 6 345 6 628 6 951 7 347 7 537 7 872 New York PrintsThisPeriod 283 323 396 190 335 New York Sales 31 588 28 654 25 846 26 844 35 155 21 035 New York PrintCount pr Sale 0,00988 0,01250 0,01475 0,00540 0,01593 New York Difference in PrintsThisPeriod from Previous 0,00262 0,00225 -0,00935 0,01053 New York %Difference 26,52% 18% -63,38% 195%

Records in orange is what we try to accomplish.

I have enclosed sheet for reference.

• ###### 1. Re: How to calculate the difference from previous period over two data sources with a counter value

Hi Sten,

They could be nested table calculations

(a table calc referencing another table calc).

Yours,

Yuri

1 of 1 people found this helpful
• ###### 2. Re: How to calculate the difference from previous period over two data sources with a counter value

Did not find any attachment...

• ###### 3. Re: How to calculate the difference from previous period over two data sources with a counter value

Hi Sten,

To find the attachment,

you'd like to visit the web page.

No attachments in email, sorry.

Yours,

Yuri

1 of 1 people found this helpful