CODEWITHSUNDEEP
tableau-api
xplorerdev
xplorerdev

Reputation: 375

How to use Tableau TOTAL() on WINDOWS_SUM()

In my tableau workbook, I have a calculated field - "Rolling 12 Month Sales" having the below formula and its working fine.
WINDOWS_SUM(SUM(Sales),-11,0)

Now , I am trying to achieve a Rolling 12 Month Sales % measure.

For this % measure, I am referring an existing calculation - SUM(Sales)/TOTAL(SUM(Sales)) - When this is cut on various segments, I get the percent distribution.

I am trying to get the exact same thing, on the Rolling 12 Month Sales % calculation. I tried the following but its not allowing to use a table calculation inside TOTAL()
WINDOWS_SUM(SUM(Sales),-11,0)/TOTAL(WINDOWS_SUM(SUM(Sales),-11,0))

Original Data
+--------+----------+----------+
| Month | Hardware | Software |
+--------+----------+----------+
| Jan-20 | 5000 | 7500 |
| Feb-20 | 6500 | 10000 |
| Mar-20 | 8000 | 10500 |
| Apr-20 | 11000 | 15000 |
| May-20 | 13500 | 21000 |
+--------+----------+----------+

Rolling 2 Months Sum Sales (This is working fine)
+--------+----------+----------+
| Month | Hardware | Software |
+--------+----------+----------+
| Jan-20 | 5000 | 7500 |
| Feb-20 | 11500 | 17500 |
| Mar-20 | 19500 | 28000 |
| Apr-20 | 25500 | 35500 |
| May-20 | 32500 | 46500 |
+--------+----------+----------+

Rolling 2 Months Sum Sales % - Below are the nos I am trying to achieve.
+--------+----------+----------+
| Month | Hardware | Software |
+--------+----------+----------+
| Jan-20 | 40.00% | 60.00% |
| Feb-20 | 39.66% | 60.34% |
| Mar-20 | 41.05% | 58.95% |
| Apr-20 | 41.80% | 58.20% |
| May-20 | 41.14% | 58.86% |
+--------+----------+----------+

Running out of options!

Best Regards

Upvotes: 1

Views: 449

Answers (1)

Andy TAR Sols
Andy TAR Sols

Reputation: 1735

There should be no need to TOTAL a WINDOW_SUM. I suspect this could be solved with different Compute Using. But first I don't fully understand why you're taking the approach you are attempting. Any chance you could show some sample data with the results you expect along the way? For simplicity in the example it would be easier to do a rolling 2 periods rather than 12.

Upvotes: 0

Related Questions