
1. Re: MAT Calculation
Mihai Constantinescu Jul 20, 2017 5:20 PM (in response to Alex Tudose)1 of 1 people found this helpfulSalut , 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 tea ajutat !
Mihai

2. Re: MAT Calculation
Mihai Constantinescu Jul 21, 2017 7:00 AM (in response to Mihai Constantinescu)Era ce vroiai sau not there yet ?

3. Re: MAT Calculation
Alex Tudose Jul 21, 2017 8:06 AM (in response to Mihai Constantinescu)Salut Mihai
Many thanks for your reply! I've tried using WINDOW_SUM but I kept running into a mixing aggregated and nonaggregated 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 Jul15 24 24,610,840 9,656,527 Aug15 23 22,080,152 9,138,244 Sep15 22 22,597,975 9,070,853 Oct15 21 23,082,901 9,202,695 Nov15 20 23,326,440 9,397,897 Dec15 19 25,112,813 10,200,296 Jan16 18 21,714,990 9,163,256 Feb16 17 21,204,644 8,976,786 Mar16 16 23,587,985 9,812,681 Apr16 15 21,117,177 8,330,216 May16 14 24,944,455 9,640,128 Jun16 13 25,012,763 9,646,332 Jul16 12 22,882,544 8,996,110 Aug16 11 24,812,954 9,733,209 Sep16 10 23,702,973 8,964,849 Oct16 9 24,781,318 9,569,681 Nov16 8 25,090,217 9,592,562 Dec16 7 24,181,829 9,995,658 Jan17 6 24,382,440 9,182,559 Feb17 5 20,616,103 8,021,233 Mar17 4 23,732,191 9,080,576 Apr17 3 23,191,916 8,816,829 May17 2 25,047,603 9,438,357 Jun17 1 26,236,356 9,239,454 PS: Can't attach Excel therefore here are the intermediary calculations:

4. Re: MAT Calculation
Alex Tudose Jul 21, 2017 8:06 AM (in response to Mihai Constantinescu)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

share trends.xlsx 26.5 KB


5. Re: MAT Calculation
Mihai Constantinescu Jul 21, 2017 8:51 AM (in response to Alex Tudose)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

6. Re: MAT Calculation
Alex Tudose Jul 21, 2017 9:59 AM (in response to Mihai Constantinescu)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.

share trends.twbx 48.0 KB



8. Re: MAT Calculation
MANI BOJJA Jul 21, 2017 1:52 PM (in response to Alex Tudose)1 of 1 people found this helpful 
9. Re: MAT Calculation
Mihai Constantinescu Jul 21, 2017 4:20 PM (in response to Mihai Constantinescu)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.

share trends.twbx 304.7 KB


10. Re: MAT Calculation
Alex Tudose Jul 22, 2017 11:28 AM (in response to Mihai Constantinescu)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

share trends v1.twbx 112.9 KB

image001.jpg 21.7 KB


11. Re: MAT Calculation
Alex Tudose Jul 22, 2017 11:35 AM (in response to MANI BOJJA)Hi Mani  thanks for answering the MAT question! I can only see a screenshot, is there an attachment?

12. Re: MAT Calculation
MANI BOJJA Jul 23, 2017 10:05 PM (in response to Alex Tudose)Here you go Alex.

share trends.twbx 77.2 KB


13. Re: MAT Calculation
Mihai Constantinescu Jul 24, 2017 5:05 AM (in response to Alex Tudose)1 of 1 people found this helpfulDid 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...

14. Re: MAT Calculation
Alex Tudose Jul 25, 2017 7:15 AM (in response to Mihai Constantinescu)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