1 2 Previous Next 27 Replies Latest reply on Mar 26, 2018 5:45 AM by daniel.andell

# Running total acc per month

I would like to make a running 12-month chart from the data I have in my example. But I would like to do this a new way. I would like it to show the last active month last. Since it is February now I would like to have February in the back and have 12 months lined up from that.
Instead of showing the value for each month I would like each month to show the total of the last 12 months until that specific month.

So for example we have the following data

Now I would like to show the following in the running chart. That means February takes the sales accumulated from the last 12 months and the same goes for all months.

• ###### 1. Re: Running total acc per month

Hi Daniel,

Find my approach stored in the attached workbook v10.5.

~Tushar

• ###### 2. Re: Running total acc per month

Thanks Tushar  More i dont think this is what I wanted though.

December 2017 for example should be 769 040 (jan-dec sum)

and February 2018 should be 708700 (mar-dec 2017 + jan-feb 2018)

• ###### 3. Re: Running total acc per month

Anyone who can help me solve this?

• ###### 4. Re: Running total acc per month

Tushar  More do you think I could use a moving calculation?

• ###### 5. Re: Running total acc per month

Hi

see the attached this one was a little tricky

the formula you want is window_sum(sum(sales),-11,0)

but you have to set the calculation order as shown with the sort order on Date Max Desc

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 6. Re: Running total acc per month

Thanks Jim Dehner

This is perfect. Thanks a lot.

Just one follow up question. Now I have the numbers correct, but I only want to show them for the last 12 months. How can I make it show last 12 months and still have the full calculation as in your example?

• ###### 7. Re: Running total acc per month

Ok here is how

You need to add the date to Context - move it to the filter shelf

then yu can select the dates you want to show from the filter

you can place all the dates in a single file like this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 8. Re: Running total acc per month

Thanks again Jim Dehner

I am not sure this works though. I tested it below and when I filter the numbers are also changing.

I would like to use the acc total and only show the last 12 months with that result. So the numbers in red on the right should only show.

I attached my example where I followed your steps.

Hope this makes sense

• ###### 9. Re: Running total acc per month

Right you are - sorry

see the attached

I had to alter the approach to get a unique mark for the month-year

next

set the calculation order as shown

Jim

1 of 1 people found this helpful
• ###### 10. Re: Running total acc per month

Great Jim Dehner

It works just the way I wanted too. Just a final question. Is there a way to make the hide automatic. I have an SQL server that updates every day. Can I make it only show the last 12 months without manual work every time I get a new month?

• ###### 11. Re: Running total acc per month

Thanks

This was an interesting problem - we don't usually look at dates in the reverse order - brought up some different issues

Jim

• ###### 12. Re: Running total acc per month

Jim Dehner I think this can be a powerful chart. I create reports for our organisation and it is important that all reports work on their own. If this is not possible it would mean that someone manually has to update.

Do you think there is a way to solve this?

• ###### 13. Re: Running total acc per month

Hi

See the attached - this might work but you will have to try it when you update the file with more data (i.e. extend the date range)

I saw something Shin worked that made me think of this

Started by showing all the dates then I added First() to the viz then created a calculate filed           if first()>-12 then 1 else 2 end     so that only the first 12 values are in group 1 the rest ore in 2

Then added the calculation to filter and set to 1 - see below

everything else is the same

I hid the the calculations on the rows shelf by un-selecting show header and this is what you get the Group 2 data is "Hidden" and the Group 1 data is the

12 most recent months

I think this will work when you update the file -

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 14. Re: Running total acc per month

Thanks again for all your help Jim Dehner it is perfect. I will extra careful after next month end.

1 2 Previous Next