1 2 Previous Next 17 Replies Latest reply on Jul 27, 2017 2:07 PM by Alex Tudose

# MAT Calculation

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!

• ###### 1. Re: MAT Calculation

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

1 of 1 people found this helpful
• ###### 2. Re: MAT Calculation

Era ce vroiai sau not there yet ?

• ###### 3. Re: MAT Calculation

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

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

• ###### 5. Re: MAT Calculation

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

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.

• ###### 7. Re: MAT Calculation

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

• ###### 8. Re: MAT Calculation

Hi Alex,

Here is my solution. Let me know if that works.

Thanks,

Mani

1 of 1 people found this helpful
• ###### 9. Re: MAT Calculation

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.

1 of 1 people found this helpful
• ###### 10. Re: MAT Calculation

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

• ###### 11. Re: MAT Calculation

Hi Mani - thanks for answering the MAT question! I can only see a screenshot, is there an attachment?

• ###### 12. Re: MAT Calculation

Here you go Alex.

1 of 1 people found this helpful
• ###### 13. Re: MAT Calculation

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...

1 of 1 people found this helpful
• ###### 14. Re: MAT Calculation

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

1 2 Previous Next