14 Replies Latest reply on Apr 5, 2017 7:37 AM by rajeev kumar

    Multiple aggregation in text table

    rajeev kumar

      Is there a way to achieve something like this in Tableau? My requirement is to calculate TOTAL MTD, TOTAL YTD and % of tatal MTD&YTD.

       

      CountryQuantitySales Total
      USA ####
      CHINA####
      MAXICO####
      INDIA####
      TOTAL MTD####
      % of TOTAL MTD%%%%
      TOTALYTD####
      % of TOTAL YTD%%%%

       

       

      thanks in advance.

        • 1. Re: Multiple aggregation in test table
          Lucie Brett

          Hello Rajeev,

           

          I would say that anything is possible but it would really help the forum to help you if you share a workbook as then we could take a look at the data!

          • 2. Re: Multiple aggregation in test table
            rajeev kumar

            Thanks for such a prompt reply. I am using "Sample - Superstore Sales (Excel)" data to achieve the above requirement. Attached sample data for your reference.

            • 3. Re: Multiple aggregation in test table
              Lucie Brett

              Hello Rajeev,

               

              The data that you have sent doesn't have countries and it isn't possible to show the true MTD and YTD as the last transaction is in 2012.

               

              I have got your started though in the attached, I have added a parameter to make today 5 years ago. I have then used this to get the YTD and MTD quantity and sales.

               

              Do these calculations help?

               

              When you say % of total MTD/YTD are you referring to the total of those four countries as a percentage of all countries?

              • 4. Re: Multiple aggregation in text table
                rajeev kumar

                Hi Luicie,

                I assume your solution might work for me. I am not able to open the workbook as you appears to use higher version of tableau( I am using 10.0.2).

                 

                by % of total MTD/YTD,  I am referring to the total of those four countries as a percentage of all countries.

                 

                can you please write down steps how you achieved multiple aggregations so that i can try same on my version of tableu. Thanks again.

                 

                Rajeev

                • 5. Re: Multiple aggregation in text table
                  Lucie Brett

                  Hello Rajeev,

                   

                  I created a calculated field which identified the "Four countries":

                   

                  If [Region] = 'Atlantic' then 'Four countries'

                  elseif [Region] = 'Quebec' then 'Four countries'

                  elseif [Region] = 'Ontario' then 'Four countries'

                  elseif [Region] = 'West' then 'Four countries'

                  else 'Other regions'

                  end

                   

                  Then to get the total for those four, I added another calculated field "Four countries MTD quantity":

                   

                  if [Regional breakdown] = 'Four countries' then [MTD Quantity]

                  else null

                  end

                   

                  Then to calculated the %, another calculated field "%MTD":

                   

                  SUM([Four countries MTD Quantity])/SUM([MTD Quantity])

                   

                  By the way, the "MTD Quantity" calculated field that I used last night is:

                   

                  IF [Order Date] <= [Today 5 years ago]

                  AND DATEDIFF('month',[Order Date], [Today 5 years ago])= 0 THEN [Order Quantity] END

                   

                  Remember I added a parameter to make a date 5 years ago but usually this would be the function today().

                   

                  To get everything to appear in one table, you might need to house both parts on different worksheets and then put them together on one dashboard to make it look like they are part of the same table.

                   

                  Hope that helps!

                  1 of 1 people found this helpful
                  • 6. Re: Multiple aggregation in text table
                    rajeev kumar

                    Hi Luicie,

                    Thanks for your solution but the downside of using different worksheet in one dashboard is

                    1. when you export to excel, it will only show data for one workbook( currently selected).

                    2. when you exclude a column from main worksheet,  the same will not reflect on the other one.

                     

                    thanks,

                    Rajeev

                    • 7. Re: Multiple aggregation in text table
                      Lucie Brett

                      Hi Rajeev,

                       

                      This might not be a popular thought on this forum but if you will need to export to Excel, maybe these calculations/visualisations could be done in Excel?

                       

                      I have to say that I find using Tableau to present tables is often more painful!

                      • 8. Re: Multiple aggregation in text table
                        Nagarajan R


                        Hi Rajeev,

                        I couldn't spend much time but with whatever time I had, I have put together a workbook for your reference. It may be of help!!!


                        Thank you

                        Nagarajan

                        • 9. Re: Multiple aggregation in text table
                          Nagarajan R

                          Screenshot for quick reference

                           

                          • 10. Re: Multiple aggregation in text table
                            rajeev kumar

                            Thanks for your time Nagarajan.

                             

                            My Requirement is to put these calculations in Rows and not column but this can be a good alternative to the original layout I am looking for.

                             

                            I have suggested both these options( yous and Luicie's ) to my team. Lets see what is getting finalized.

                             

                            Thanks to both of you.

                            • 11. Re: Multiple aggregation in text table
                              Nagarajan R

                              Hi Rajeev,

                               

                              I focused only on calculation logic and not the layout yesterday. But can give it a try.

                              Please let me know what you are seeing Quantity and Sales Total again each country row? Is it per day's quantity or MTD quantity or selected date range quantity etc?

                               

                              One advantage you will have with this approach is the MTD or YTD logic can be applied for any dimension and filter selections can also be dynamic. If you are not hard coding the dimension filter selection for your MTD or YTD calcs.

                               

                              Let me know and I will give it a try.

                               

                              Thank you

                              Nagarajan

                              • 12. Re: Multiple aggregation in text table
                                rajeev kumar

                                Hi Nagarajan,

                                it will be super helpful if you can acheive this layout in one worksheet.

                                MTD is total of this month/ total of all.

                                 

                                At this moment, i am more interested in layout than correct data( as we are in POC phase)

                                 

                                thanks,

                                Rajeev

                                • 13. Re: Multiple aggregation in text table
                                  Nagarajan R

                                  Is your data at Date or Month level?

                                  I still don't get what you want to show under Quantity or Sales Total against each Country record?

                                   

                                  I thought MTD is an aggregation of 1st date of the selected month to the selected date of the same month. Is this understanding not correct?

                                   

                                  It will be really helpful if you can put/show some sample data and report with values you want in an excel sheet (I understand you are using sample superstore but I would suggest you to build a sample base data set in XL and build the report structure in XL again so we can understand how you want to aggregate or show values including dimensions in your report.

                                   

                                  I get your point on layout being your priority but the way data is calculated or presented will play a key part in re-aligning the layout as there could be multiple ways or options we can explore then.

                                   

                                  Thanks

                                  Nagarajan

                                  • 14. Re: Multiple aggregation in text table
                                    rajeev kumar

                                    I work with very sensative data so unfortunatly cant share the actual sheet but yeah let me prepare a dummy excel sheet for you and share so that you can try.

                                     

                                    Meanwhile if you want you can use whatever data you want to use for this kind of layout.

                                     

                                    will reply you as soon as data is ready with me. thanks