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

    MAT Calculation

    Alex Tudose

      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
          Mihai Constantinescu

          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
            Mihai Constantinescu

            Era ce vroiai sau not there yet ?

            • 3. Re: MAT Calculation
              Alex Tudose

              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 ACompany B
              Jul-1524 24,610,840    9,656,527
              Aug-1523 22,080,152    9,138,244
              Sep-1522 22,597,975    9,070,853
              Oct-1521 23,082,901    9,202,695
              Nov-1520 23,326,440    9,397,897
              Dec-1519 25,112,813 10,200,296
              Jan-1618 21,714,990    9,163,256
              Feb-1617 21,204,644    8,976,786
              Mar-1616 23,587,985    9,812,681
              Apr-1615 21,117,177    8,330,216
              May-1614 24,944,455    9,640,128
              Jun-1613 25,012,763    9,646,332
              Jul-1612 22,882,544    8,996,110
              Aug-1611 24,812,954    9,733,209
              Sep-1610 23,702,973    8,964,849
              Oct-169 24,781,318    9,569,681
              Nov-168 25,090,217    9,592,562
              Dec-167 24,181,829    9,995,658
              Jan-176 24,382,440    9,182,559
              Feb-175 20,616,103    8,021,233
              Mar-174 23,732,191    9,080,576
              Apr-173 23,191,916    8,816,829
              May-172 25,047,603    9,438,357
              Jun-171 26,236,356    9,239,454

               

              PS: Can't attach Excel therefore here are the intermediary calculations:

               

              • 4. Re: MAT Calculation
                Alex Tudose

                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
                  Mihai Constantinescu

                  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

                    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
                      Mihai Constantinescu

                      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

                      MTA.png

                      • 8. Re: MAT Calculation
                        MANI BOJJA

                        Hi Alex,

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

                         

                        Screen Shot 2017-07-21 at 3.50.59 PM.png

                         

                        Thanks,

                        Mani

                        1 of 1 people found this helpful
                        • 9. Re: MAT Calculation
                          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:

                          compute.png

                           

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

                           

                           

                          shares.png

                           

                          share.png

                           

                          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
                            Alex Tudose

                            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
                              Alex Tudose

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

                              • 12. Re: MAT Calculation
                                MANI BOJJA

                                Here you go Alex.

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

                                  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
                                    Alex Tudose

                                    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