-
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 te-a 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 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:
-
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