4 Replies Latest reply on Sep 19, 2018 10:11 PM by Kylie Kaye

Calculate the difference between a running total rank

Hello

I'm trying to calculate the difference between running total ranks over a 12 month period so that I can put the calculation on colour and show how different categories have changed rank (by running total) over time (see below image and attached workbook).

Grateful for any help! • 1. Re: Calculate the difference between a running total rank

Okay, so let's see if this works. Start by turning your table calculation into a few separate calculated fields:

Order Count

COUNTD([Order ID])

Order Count Running Sum

RUNNING_SUM([Order Count])

Order Count Running Sum Rank

RANK([Order Count Running Sum])

Order Count Running Sum Rank will then replace your existing pill/table calc.

Then create one more calculated field:

Difference

[Order Count Running Sum Rank]-LOOKUP([Order Count Running Sum Rank],-1)

Drag Difference up to the color card and set it to calculate as follows: Be sure to set it to Restart at every Sub-Category or else it will base the color of January on the difference between January and the previous sub-category's December.

You'll now have something like this: Is that what you're looking for? If so, see attached workbook.

• 2. Re: Calculate the difference between a running total rank

Following up on this one. Let us know how we can help further.

• 3. Re: Calculate the difference between a running total rank

Sorry for the delay in getting back to you, Ken. This is great. My only problem is that I don't seem to be able to choose the 'sub-category' equivalent as the 'restarting every' option in my actual workbook. I'll keep playing around with it, but if you have any ideas, grateful to hear them.

• 4. Re: Calculate the difference between a running total rank

Figured it out - my Running Sum Rank was being calculated across instead of down. I changed it to calculate by 'Subcategory' and that did the trick. Thanks again for all your help!