5 Replies Latest reply on Jul 30, 2015 11:42 AM by Elodie Gbedan

# Calculate a multiple ratio

Hello

I would like to calculate the multiple for each of the 52 weeks for data range going from 2006-2014

Essentially the multiple formula is

Multiple Sales Week N= (Sales from week N+1 to Week 52)/Week N

So multiple for week 1= Sales week 2 to week 52/Sales week 1

I suspect there might be a running sum included here, but I am not really sure of this.

Thanks,

Elodie

• ###### 1. Re: Calculate a multiple ratio

Elodie,

This may be a starting point for you.

I did create a running sum:

RUNNING_SUM(SUM([Value]))

Calculated total for the year

{ FIXED [Year]:SUM([Value])}

Then Calculated the Multiple Sales

(ATTR([Total for Year])-[RunningSum])/(ATTR([Value]))

Both the RunningSum and Mult Sales had a "Compute using" of 'Week'

• ###### 2. Re: Calculate a multiple ratio

Thanks so much for your help.

You really capture the idea of the metric. I did try and I can't get the Mult Sales formula to populate. I pretty much copied the formula as laid out in the test workbook  you sent back. When I dragged it to the shelf, it wouldn't show the result of the calculation. It is empty.

I am not sure if there are some settings I am missing

Thanks

Elodie

• ###### 3. Re: Calculate a multiple ratio

Elodie,

I'm not sure what could be the cause.

The only settings involved would be making sure Year and Week are on some shelves

and changing the "Compute using" to "Week".

Are you permitted to share your workbook?

If not, can you anonymize it for us: http://community.tableau.com/docs/DOC-6271

Otherwise, would be grateful if you would post screen shots of your worksheet

with the pills in place, and of the "Compute using" settings.

• ###### 4. Re: Calculate a multiple ratio

Hi

Unfortunately, I can't publish the workbook. I will look into how to anomymize the workbook.

Also, with the suggested method, would I be able to perform the calculation at a lowest level. The lowest level of my data set is sku level. I want to set the formula once so when I drill down, it would perform this calculation at every level possible.

Thanks,

Elodie

• ###### 5. Re: Calculate a multiple ratio

I was wondering if LoD will be helpful

thanks