1 2 3 Previous Next 31 Replies Latest reply on Jul 19, 2018 12:58 AM by jo.bi

    Cumulative SUM calculation

    Raghu Anisur

      Hi ,

       

      Attached is my package.

       

      I want to calculate cumulative SUM for my SALES measure.I have 3 measures

      1.sales measure as it is

      2.sales running total by order date

      3.% share of sales

       

      I have the problem with the calculation 2.

       

      If i want to calculate the cumulative SUM of sales, means...if i select January, it shows the january data, after that if i select feb month, it has to add up the january data and show the cumukative SUM instead of picking the just feb data, it has to pick previous months data.

       

      How can this be possible in tableau.i tried with YTD total by order date but it sis not working.

       

      Please guide me if anyone knows about this.

       

       

      Regards,

      Anisur

        • 1. Re: Cumulative SUM calculation
          Bora Beran

          Instead of using Month(Year) as a filter use

           

          WINDOW_MAX(ATTR(DATENAME('month',[Order Date])))

           

          Table calcs hide marks when you use them as filters so your running sum will still take into account the previous months and give you the correct value even if they are not visible.

          1 of 1 people found this helpful
          • 2. Re: Cumulative SUM calculation
            Raghu Anisur

            Hi,

             

             

            I have created with the calculated field with “WINDOW_MAX(ATTR(DATENAME('month',[Order Date])))

             

             

             

            And dropped this on to fileter shelif and made quick filter , but still the functionality is not working correctly when tested.

             

            Any of your changes in the workbook would be greatly helpful.

             

             

             

            Regards,

             

            Anisur

            • 3. Re: Cumulative SUM calculation
              Raghu Anisur

              Hi Bora,

               

              What I mean here is , the month selection will add up the values of previous months and current month here ..Cumulative sum for the current selection of month from the previous months.

               

              Any idea how can we do this in tableau.

               

              Regards,

              Anisur

              • 4. Re: Cumulative SUM calculation
                Bora Beran

                It seems like it is already doing that.

                1 of 1 people found this helpful
                • 5. Re: Cumulative SUM calculation
                  lei.chen.0

                  Hello Raghu,

                   

                  Try filter by index().

                   

                  If filter by month, the values not in the table will not be accumulated.

                  Because it's table calculation

                   

                  While filter by index(), I feel like the values are still in the table but not visible.

                  So these visible values are still accumulated.

                   

                  Please refer to the attached workbook for details.

                   

                   

                  Regards.

                  • 6. Re: Cumulative SUM calculation
                    Bora Beran

                    Lei,

                    WINDOW_MAX(ATTR(DATENAME('month',[Order Date]))) is also a table calculation and it won't filter out rows if you use it as a quick filter.


                    While INDEX() will also hide since it is a table calc like WINDOW_MAX, it wouldn't work in this case since Raghu is trying to expose the filter as quick filter. INDEX() as quick filter would show 1,2,3,....12 not January, February... and depending on what months have data for a given year, it will be inconsistent. E.g. if there is no data in January and 2011 data starts with February, February would correspond to 1 etc. which would be very confusing to use as a filter.


                    Thanks,


                    Bora

                    1 of 1 people found this helpful
                    • 7. Re: Cumulative SUM calculation
                      lei.chen.0

                      Bora Beran wrote:


                      While INDEX() will also hide since it is a table calc like WINDOW_MAX, it wouldn't work in this case since Raghu is trying to expose the filter as quick filter. INDEX() as quick filter would show 1,2,3,....12 not January, February... and depending on what months have data for a given year, it will be inconsistent.

                       

                      Yes, you are right

                       

                       

                      Regards.

                      1 of 1 people found this helpful
                      • 8. Re: Cumulative SUM calculation
                        Raghu Anisur

                        Hi Bora or Lie,

                         

                        Thanks for your solution and great insights. its perfectly  working  now for the Cumulative SUM of sales for the previous months.

                         

                        I have the Percent Total for the sales for the current months, similarly how can we calculate the percent total for the cumulative months.

                         

                        Ex: If I select Feb month( it takes the January+Feb month) , I want to calculate the percent total for this cumulative month, how can we do this in tableau.

                         

                        Any ideas on this would be greatly helpful .Attached is the tableau package.

                         

                        Regards,

                        Anisur

                        • 9. Re: Cumulative SUM calculation
                          Raghu Anisur

                          Hi Bora,

                           

                          How do we calculate the percentage total for the cumulative sum of sales here.

                           

                          Any suggestion would be a great helpful.

                           

                          Regards,

                          Anisur

                          • 10. Re: Cumulative SUM calculation
                            Raghu Anisur

                            Hi Bora,

                             

                            I want to calculate the % Total for the Cumulative SUM , I tried all the approached like secondary table calculations. It doesn't work for me.

                             

                            Please let me know any idea or suggestion to calculate the % Total for the Cumulative SUM sales .

                             

                            Attached is my package.

                             

                            Greatly appreciate your insights and suggestions.

                             

                            Regards,

                            Anisur

                            • 11. Re: Cumulative SUM calculation
                              Bora Beran

                              You need to do

                               

                              RUNNING_SUM(SUM([Sales]))/WINDOW_SUM(SUM([Sales]))

                              • 12. Re: Cumulative SUM calculation
                                Raghu Anisur

                                Hi Bora,

                                 

                                Thanks so much for responding on this.

                                 

                                I have created the formula     RUNNING_SUM(SUM([Sales]))/WINDOW_SUM(SUM([Sales]))

                                 

                                And dropped on filter, and I am not sure how to calculate the % Total for the Cumulated months have created for the normal months  with simple table % Total.

                                 

                                But how do we calculate the % Total for the Cumulated Months or in the other meaning % Total for the Sales YTD.

                                 

                                Attached is the package.

                                 

                                Greatly appreciate your help on this..

                                 

                                Regards,

                                Anisur

                                • 13. Re: Cumulative SUM calculation
                                  Bora Beran

                                  I thought you wanted to put this on measure values card. Why did you put it on filter? That formula is the % total for cumulated months. It will give 100 percent in the last value in your table.

                                  1 of 1 people found this helpful
                                  • 14. Re: Cumulative SUM calculation
                                    Raghu Anisur

                                    Hi Bora,

                                     

                                    I have applied the % Total  the same way how I did previously , the % numbers are giving wrong values  or I am doing wrong way to calculating the % Total for the Cumulative Months.

                                     

                                    Not sure how  to use the % Total for the derived measure, I did on direct measure easily.

                                     

                                    The same way I did but it is giving the different result set.I request you to  kindly look into this.

                                     

                                    Greatly appreciate your response and patience for looking into this.

                                     

                                    Regards,

                                    Anisur

                                    1 2 3 Previous Next