11 Replies Latest reply on Apr 25, 2018 5:17 AM by Zhouyi Zhang

# Rolling Saving Calculation using week numbers

Hi All,

I'm hoping you can help me with a rolling calculation for a workbook I'm building.

I have values in the data collected on a weekly basis

Firstly I want to use the last week of 2017 as the baseline amount (week 53) - I then want to subtract the value from week 4 of 2018 off week 53 value.

so for example week 53 value 1000  - week 4 value 800 = 200

I then want to subtract week 4 value from week 8 value

so for example week 4 value 800 - week 8 value 780 = 20

I want keep rolling my calc in this way on a 4 weekly cycle BUT accumulating the amounts from each calculation. So using the example above the amount my calc would display is 220

I hope this makes sense but please let me know if not.

• ###### 1. Re: Rolling Saving Calculation using week numbers

Hi, Tony

Is there any sample workbook can be shared?

ZZ

• ###### 2. Re: Rolling Saving Calculation using week numbers

Hi ZZ,

Any help greatly appreciated

Thanks

Tony

• ###### 3. Re: Rolling Saving Calculation using week numbers

Hi, Tony

thanks for the workbook, it does help!

I tried to understand your logic, and below is what I tried, please correct me if I misunderstand

ZZ

1 of 1 people found this helpful
• ###### 4. Re: Rolling Saving Calculation using week numbers

Hi ZZ - Wow!!! So close with what I was trying to get to. The only thing out with it is that the 4 week gap is applied a little early.

So ideally I'm looking for baseline in the first example to be the value on 25 Dec 2017 and the 29th Jan being the value we subtract so in the sheet above the 4 week gap value would be 2302 - 2285 = 17.

The new baseline would then be the 2285 value - I'm always looking to subtract the last weeks in the month and the newest would be new baseline for the next subtraction.

You are a genius though - incredibly close to what I want to achieve - thanks so much

I hope my explanation above helps but if not please ask more questions

• ###### 5. Re: Rolling Saving Calculation using week numbers

Hi, Tony

Can I translate your requirement is from the last week of the - the last week from previous month as the 4 weeks gap?

So the result should be something like this?

ZZ

1 of 1 people found this helpful
• ###### 6. Re: Rolling Saving Calculation using week numbers

Hi ZZ

Yes the screenshot you have sent would be the 4 week gap - that logic is 100% correct :-)

• ###### 7. Re: Rolling Saving Calculation using week numbers

Hi, Tony

Please find my workaround attached as well as screenshot below.

The only thing I am still thinking is how to show the total in grand total rather than in an individual column, but so far have no idea yet.

ZZ

1 of 1 people found this helpful
• ###### 8. Re: Rolling Saving Calculation using week numbers

Hi ZZ

Massive thanks for your help on tihis one - just a last couple of questions.

Total Along Table (down) column showing 58 all the way down It - is there any way I can show the 58 once in a pill? That would answer this once and for all - I don't need to do anything with grand total so not too much of a worry.

Also many thanks for sending the workbook - however I cannot open with my version of Tableau Desktop (I'm on 10.1) - my license is on a work machine too so no chance of upgrade. Is there any way you could send the workbook in an earlier version or share the calcs used to build the sheet in your screenshot?

I'm eternally grateful for your help on this one as it's going to solve a big problem!

• ###### 9. Re: Rolling Saving Calculation using week numbers

Hi, Tony

If you just need the end result of total, it will be easy.

Please find my update attached. (sorry about I dont have 10.1 installed and the earliest version 10.5 can save is10.2)

I tried to describe all steps in this view as shown below so that you can reproduce in your side. but let me know if you have any question.

Hope this will solve your problem

ZZ

• ###### 10. Re: Rolling Saving Calculation using week numbers

I got it to work from your instructions.

Truly amazing help Zhouyi Zhang - Thank you so much. Your help has been brilliant. Really appreciate it :-)

• ###### 11. Re: Rolling Saving Calculation using week numbers

No worries, I'm glad to help

Hope you will enjoy tableau

ZZ