7 Replies Latest reply on Dec 6, 2016 5:49 AM by Yuriy Fal

    Running Total in Cohort Table

    Anika Brust

      Hello everybody,

       

      I have an issue with a running total in a cohort table.

       

      For our e-commerce business I want to calculate the number of re-orders per cohort, split by the channel the buyer came to our site to.

       

      Some definitions before fun starts:

       

      Cohort month (so basicly the cohort of each buyer):    {FIXED [Buyer Id]: MIN([Order Date])}

       

      MONTH (#) (the number of following months since first purchase): DATEDIFF('month',[Cohort month], [Order Date])

       

      Channel is fixed per order.

       

      My first try to calculate the running sum of re-orders:

       

      (RUNNING_SUM(COUNTD([Order Id)]))

      -

      ATTR({ EXCLUDE [MONTH (#)]: COUNTD([# First Orders])}))

       

      looks like this:

       

      Before.png

       

      which is not so cool as months without re-orders are left empty.

       

      That's why I tried this:

       

      IF ISNULL(COUNTD([Order Id])

       

      THEN PREVIOUS_VALUE(0)

       

      ELSE RUNNING_SUM(COUNTD([Order Id]))

                -

               ATTR({ EXCLUDE [MONTH (#)]: COUNTD([# First Orders])})

      END

       

      which gives back this view:

       

      After.png

       

      This is a bit better since there are no empty cells, BUT it fills too many cells. (Yeah, I know, there is always something to complain about...)

       

      I just want each cohort to be displayed to their "biggest" [MONTH (#)]. So cohort 01 16 is right because it has 10 following months by now. But cohort 02 16 shall be displayed to month 9, cohort 03 16 to month 8 and so on...

       

      I cannot count the variations of fixed, excludes, =, <> etc. I tried. Each of them brought me nearer to shout rude words at my laptop. As there are a lot of people sitting around me and I don't want them to think I have a problem controlling my rage I hope for your support

       

      Thanks to everybody feeling with me - or even having an idea to fix my problem.

       

      Anika

        • 1. Re: Running Total in Cohort Table
          Yuriy Fal

          Hi Anika,

           

          This calc may help:

          IF INDEX() - 1 <= WINDOW_MAX( MAX( [MONTH (#)] ) )

          THEN

          RUNNING_SUM( COUNTD( [Order Id] ) ) -

          TOTAL(  COUNTD( [# First Orders] ) )

          END

          Yours,

          Yuri

          1 of 1 people found this helpful
          • 2. Re: Running Total in Cohort Table
            Anika Brust

            Hi Yuriy,

             

            thanks for your calc. I tried it and

             

            - good news: it stopped at the last MONTH (#) of each cohort

            - bad news: it fills some but not all empty cells:

             

            Calc1.png

             

            I tried to combine your and my calculations like this:

             

             

            IF INDEX() - 1 <= WINDOW_MAX( MAX( [MONTH (#)] ) )

             

            THEN

             

                 IF ISNULL(COUNTD([Order Id]))

                 THEN PREVIOUS_VALUE(0)

                 ELSE

             

                 RUNNING_SUM( COUNTD( [Order Id] ) ) -

                 TOTAL(  COUNTD( [# First Orders] ) )

                 END

             

            END

             

            This adds some 0s but doesn't give back the picture I want to have:

             

            Calc2.png

             

            Actually I'm not really sure how this calc works in detail. What does this INDEX() - 1 actually do?

             

            I cannot find a pattern when Tableau fills the cells. So I find it quite confusing to find out how to fix it.

             

            Yours,

            Anika

            • 3. Re: Running Total in Cohort Table
              Yuriy Fal

              Hi Anika,

               

              Please find the attached sample workbook

              mocked up from a Superstore dataset.

              I use [Ship Mode] as a substitute

              for your [Channel Order] dimension.

               

              I could see no contradiction with the common sense here --

              as soon as one define a Cohort as Month-Channel combination.

               

              What kind of output are you expecting, otherwise?

               

              Yours,

              Yuri

              1 of 1 people found this helpful
              • 4. Re: Running Total in Cohort Table
                Anika Brust

                Thanks for the sample workbook, should've had the idea as well.

                 

                So, I want each row to be filled up to the last month of the cohort it belongs to.

                 

                So, in your workbook when we look at August 2015:

                 

                Example.png

                 

                I want for "First Class" and "Same Day" a 0 in each column up to Month = 4.

                 

                "Second Class" should have a 2 in month 4 as well.

                 

                And "Standard Class" should be filled with number 1 up to month 4.

                 

                So that I can always find the maximum of running sum in the "last" month. This makes it easier for users to compare the Ship Modes aka Channels in each month.

                • 5. Re: Running Total in Cohort Table
                  Yuriy Fal

                  Hi Anika,

                   

                  Please find the attached.

                   

                  To make understand, please pay attention

                  to the Addressing and Partitioning (via Edit Table Calculation dialog)

                  of the Nested Table Calculation [# Reorders Running].

                  It refers to an inner Table Calc [Cohort month MAX],

                  which uses different addressing than the outer one.

                   

                  Hope it makes things working as expected.

                   

                  Yours,

                  Yuri

                  1 of 1 people found this helpful
                  • 6. Re: Running Total in Cohort Table
                    Anika Brust

                    Yuri,

                     

                    you made my day. Thanks a lot

                     

                    To also fill the NULL rows (as there are channels without any re-orders) I used this calc:

                     

                    IF INDEX() - 1 <= [Cohort Month Max]

                    THEN

                        IF ISNULL(COUNTD([Order Id]))

                        THEN PREVIOUS_VALUE(0)

                        ELSE

                        RUNNING_SUM(COUNTD( [Order Id])) -

                            TOTAL(  COUNTD( [# First Orders] ) )

                        END

                    END

                     

                    I think there is a lot more to learn about table calcs than I expected.

                     

                    Also, you introduced me to TOTAL which is a much more elegant way than using Exclude LoD which I did most of the time.

                     

                    Again, thanks and have a nice day.

                     

                    Yours,

                    Anika

                    1 of 1 people found this helpful
                    • 7. Re: Running Total in Cohort Table
                      Yuriy Fal

                      Anika, you're welcome.