2 Replies Latest reply on Jul 10, 2018 1:00 PM by Nicholas Zabilski

# How to calculate cumulitive sales on a weekly basis without using table calculations

The goal of my report is to return the maximum amount of inventory that my company has had in stores at any given time throughout the year.

My company uses week numbers instead of dates, so my data has a column for week and has values ranging from 1 - 21.

I essentially want to get a running total of sales by week number and style. I don't think that I can use a table calculation since I want to add the running sales total by week to the corresponding week's store inventory. Since we transfer product out of stores often, I want to return the maximum amount of inventory we had out in stores up to that time (which for the purpose of this project is cumulative sales + store inventory+ DC On Hand)

Below is a simple example of the math that I would like tableau to do. Current week is week 21, but as you can see for style #1 in the selected cell (going from week 15 to week 16) about 3,000 units were transferred out of the DC. I am reporting on how sales performed relative to total investment in the product, so I want to use week 15's total since it represents all of the units that we owned up until we removed units from the DC. The below is a recreation in excel, so my sales figures in the data I am using are non cumulative (what I am trying to recreate in tableau) and my "TTL" calculated field only has "store OH" and "DC OH" being summed up. Wherever Style#1 pops up I want to be able to return the number 12,613 for its "TTL"

Hopefully this makes sense, I would really appreciate some input on this!

Thank you!

• ###### 1. Re: How to calculate cumulitive sales on a weekly basis without using table calculations

Nicholas,

I'm not sure if I quite got there, but maybe this can give ideas.

I think it is possible to use Running_Sum(SUM(Sales Units)) in you calculations.

[Calculated Total] would be:

SUM([DC OH])+SUM([Store OH])+[RunningSales]

And the Max would be:

WINDOW_MAX([Calculated Total])

• ###### 2. Re: How to calculate cumulitive sales on a weekly basis without using table calculations

Hi Swaroop,

Thank you so much for taking the time to answer me and send that example! I really appreciate it. Unfortunately, I cant use table calculations since the view that I will be using this information for does not include week number. When I try to bring in a table calculation I am left with a sum of every instance of that calculation. When I try to do an average I get an error saying table calculations cant be aggregated.

I am going to type up a new post, which should hopefully be a little more concise.

Thanks again!