Tableau 10.3
Hi,
In the table below (data attached), what would be the best way to use the [Month #] field to calculate dynamic sums of the last 12 months without using rolling sums?
i.e. MAT April 2017 would be sum of sales where month # >= 2 and month # <=2+11
Ideally, the calculation would be valid as I move from months to quarters and years
Many thanks in advance!
Salut , just to be clear what do you mean by "without using rolling sums" ?
WINDOW_SUM((SUM([Sales])),-11,0) is this what you want (this basically sums from current row to previous 11)? Of course the static -11 can be replaced with a parameter and adapt to years, quarters etc.
Sper ca te-a ajutat !
Mihai
Salut Mihai
Many thanks for your reply! I've tried using WINDOW_SUM but I kept running into a mixing aggregated and non-aggregated measures...
In more detail, what I'm trying to achieve is comparing share trends for 12 month rolling sums vs. monthly share trends. I'm attaching an Excel spreadsheet with the intermediary calculations, but in essence the first image below shows the data set and the second image shows the desired result. To your prior question, I'm not sure what is the best way to approach this (i.e LOOKUP in MAT calculation, LODs, etc). but the method should allow for further development like adding product and geography filters
Multam fain!
| Company A | Company B | ||
| Jul-15 | 24 | 24,610,840 | 9,656,527 |
| Aug-15 | 23 | 22,080,152 | 9,138,244 |
| Sep-15 | 22 | 22,597,975 | 9,070,853 |
| Oct-15 | 21 | 23,082,901 | 9,202,695 |
| Nov-15 | 20 | 23,326,440 | 9,397,897 |
| Dec-15 | 19 | 25,112,813 | 10,200,296 |
| Jan-16 | 18 | 21,714,990 | 9,163,256 |
| Feb-16 | 17 | 21,204,644 | 8,976,786 |
| Mar-16 | 16 | 23,587,985 | 9,812,681 |
| Apr-16 | 15 | 21,117,177 | 8,330,216 |
| May-16 | 14 | 24,944,455 | 9,640,128 |
| Jun-16 | 13 | 25,012,763 | 9,646,332 |
| Jul-16 | 12 | 22,882,544 | 8,996,110 |
| Aug-16 | 11 | 24,812,954 | 9,733,209 |
| Sep-16 | 10 | 23,702,973 | 8,964,849 |
| Oct-16 | 9 | 24,781,318 | 9,569,681 |
| Nov-16 | 8 | 25,090,217 | 9,592,562 |
| Dec-16 | 7 | 24,181,829 | 9,995,658 |
| Jan-17 | 6 | 24,382,440 | 9,182,559 |
| Feb-17 | 5 | 20,616,103 | 8,021,233 |
| Mar-17 | 4 | 23,732,191 | 9,080,576 |
| Apr-17 | 3 | 23,191,916 | 8,816,829 |
| May-17 | 2 | 25,047,603 | 9,438,357 |
| Jun-17 | 1 | 26,236,356 | 9,239,454 |
PS: Can't attach Excel therefore here are the intermediary calculations:
Hey Mihai!
In primul rand, multumesc pentru raspuns.
Inca nu sunt acolo ☺, am postat detalii pe forum. Am incecat sa atasez si un spreadsheet, nu a mers, dar il atasez acum la email.
Multumesc inca odata, o zi/seara (depinde unde esti) frumoasa !
Alex
You lost me a bit
It would help if you can attach an updated workbook as first one didn't have this structure. If I got it right your data contains the first 4 columns - Date, Month, Mth A and B and you want MAT A and B where those are a window arg of 12 months of Mth and then do a percent diff of those 2?
O seara buna!
Mihai
Hi Mihai,
You are correct, the first workbook was different - I was looking for an answer to only a part of the problem. Then I thought that describing the broader issue might help choosing the best way to address the initial question. In summary, the data includes the monthly sales by company for 24 months. There’s’ also a ‘month #’ field with values from 1 to 24, 1 being the most recent month. The goal is to produce a chart like the one I pasted, depicting monthly and MAT share of sales for each company. As intermediary steps there should be an MAT calculation (rolling sum) then two share calculations (Mth and MAT) as % of rows. I can do this in separate tables but not in one, which I need in order to produce the Mth/MAT share chart and to apply further filters.
Maybe it’s the right time to mention that I’m fairly new to Tableau (but not to BI in general) ☺
I’ve attached the new workbook
Many thanks and happy weekend if I don’t hear from you.
One step closer ... still need to hide values that don't have full 12 previous months and calculate your share percentage ...how is the % computed, I noticed it's not a percent difference (A-B)/B
Hi Alex,
Here is my solution. Let me know if that works.
Thanks,
Mani
Done ... I initially didn't see you had more columns there and now deducted the formula for share
e.g.
WINDOW_SUM([Mth Company B ],-11,0)/
(WINDOW_SUM([Mth Company A],-11,0)+WINDOW_SUM([Mth Company B ],-11,0))
So to recap the Mth Company is:
SUM(IF [Company]="Company A" then [Sales] else 0 END) the other is mirrored
MAT Company is :
WINDOW_SUM([Mth Company A],-11,0) same for B
compute using:
MAT Share Comp A =
WINDOW_SUM([Mth Company A],-11,0)/
(WINDOW_SUM([Mth Company A],-11,0)+WINDOW_SUM([Mth Company B ],-11,0))
Let me know if you can open workbook, I only have tab 10.3 on my laptop .. or if you have questions.
Great work, many thanks! Fully answers the question, as it was formulated.
One last thing: in the actual data, companies are not the same, they change with the data (i.e. by filtering on geography or product lines)
Can the denominator in the calculations for share be changed from sum of sales for each given company to sum of sales for all companies? This way I can use a ‘Company’ parameter to display the chart below for each company. I’ve tried using FIXED , but I’m obviously missing something.
For a better illustration, I’ve added a ‘chart’ tab to your solution. Let me know of what’s possible.
Cheers
Hi Mani - thanks for answering the MAT question! I can only see a screenshot, is there an attachment?
Did you try a
CASE CompanyParam
WHEN "Company A" then
WINDOW_SUM([Mth Company A],-11,0)/
(WINDOW_SUM([Mth Company A],-11,0)+WINDOW_SUM([Mth Company B ],-11,0))
WHEN "Company B" then
WINDOW_SUM([Mth Company B],-11,0)/
(WINDOW_SUM([Mth Company A],-11,0)+WINDOW_SUM([Mth Company B ],-11,0))
..............
END
or I didn't get what you want correctly...
Hi Mihai,
Thanks for sharing the parameter calculation.
What I really wanted to ask was if there was a way to replace the share calculation denominator i.e. (WINDOW_SUM(,-11,0)+WINDOW_SUM(,-11,0) with a formula that sums the sales for all companies, without having to add them to the calculation one by one. I’ve tried using FIXED, but I’m obviously missing something.
Cheers
