8 Replies Latest reply on Jun 21, 2018 9:49 AM by Denis Kelly

    Counting "removed" items

    Denis Kelly

      Hi – I need to create a counting measure in Tableau that will measure removals of an account in a list from one month to the next.  This will be executed for the current months/periods and all prior periods.  Not sure if this is better handled in SQL but putting it out there.

       

      It could be the most recent month/period compared to the month/period just prior or it could be measuring a month/period that occurred last year compared to the month/period just prior to that one.  The chart below is a guide.

       

      Excel counting image 06 14 18.PNG

       

      Also attaching a tableau workbook that demonstrates the count needed and resulting measures.  The real key is the "running total" worksheet/viz.  Any help appreciated!

       

      Thanks,

      Denis

        • 1. Re: Counting "removed" items
          Yuriy Fal

          Hi Denis,

           

          You may want to look at this workbook as an example (shameless plug):

           

          Cohort Analysis : Reactivation, Retention, Churn

           

          In the example the Churn is the same nature

          as the metric you're trying to build.

           

          Hope it could help a bit.

           

          Yours,

          Yuri

          • 2. Re: Counting "removed" items
            Denis Kelly

            Hi Yury - thanks so much for the reply!  These explanations are closer than anything I have seen so far and I think I get what is being communicated but am not sure it covers it all.  At its lowest level I am looking for multiple months/time periods being displayed for this customer movement (or churn) - I did see a 3 month window in one example but I am looking for up to at least a year for summary of this information.  Do you think this is possible?

             

            Thanks again,

            Denis

            • 3. Re: Counting "removed" items
              Yuriy Fal

              Hi Denis,

               

              In my workbook there is a Parameter to choose a Period,

              and it could be Year, Quarter or Month.

               

              Following the workbook logic, Churn is defined as

              Customers present in the previous Period,

              but absent in the current one.

               

              What would be your Churn logic that differs?

               

              Yours,

              Yuri

              • 4. Re: Counting "removed" items
                Denis Kelly

                Hi Yuri - the Churn definition matches but I guess it is my understanding of how time runs over the axis.  The screen shot shown here shows multiple months and I am looking to expand the # of months being reviewed.  So, like the below chart but showing at least 12 months.  Hope that makes sense?

                 

                Thanks!!

                 

                Image for multiple months 06 18 18.PNG

                • 5. Re: Counting "removed" items
                  Yuriy Fal

                  Denis, i beg your pardon for missing your data structure completely.

                   

                  You have all Counts as the Aggregates (by Week #).

                   

                  My example would work with a different data shape --

                  they're all user transactions for at least 3 periods (minimum 3 months).

                  The raw data is at least at the granularity of [Customer ID] & [Period].

                   

                  Since there's no [Customer ID] in your dataset, my calc logic --

                  based on COUNTD([Customer ID]) -- couldn't be applied.

                   

                  Sorry about moving in a wrong direction.

                   

                  Yours,

                  Yuri

                  • 6. Re: Counting "removed" items
                    Denis Kelly

                    Hi Yuri,

                     

                    No need to apologize as you have been a tremendous help so far!  However, although you state I do not have a Customer ID field there is a "Seller ID" field and a "Seller Nm" or (Seller Name) field which should act in an identical fashion to the Customer ID field, as I understand it.  Do you still feel this can't be resolved?

                     

                    For the requirement of data to exist at the level of [Customer ID] & [Period] I would think this can be created in my db as a concatenated field.  Or am I misunderstanding that?

                     

                    Lastly, as far as Weeks vs. Months aren't they all just time periods?  Ultimately, I would like to aggregate at both levels (Week and Month) but I believe starting with Week with provide the best analysis.  This goes back to the original question of whether or not multiple time periods (for example, at least 26 weeks or 6 months) can be shown on a visualization with each independent months data of "churn" or accounts removed noted independently?

                     

                    Many thanks,

                    Denis

                    • 7. Re: Counting "removed" items
                      Yuriy Fal

                      Hi Denis,

                       

                      Could you please explain a bit about your data?

                      What do the Dimensions and Measures mean?

                      At least the ones below -- the [Removed?] Dim

                      and all the Measure Names in Columns, please:

                      New Counting example 06 13 18.png

                       

                      Please find the attached w/Sheet 7, too.

                       

                      Yours,

                      Yuri

                      • 8. Re: Counting "removed" items
                        Denis Kelly

                        Hi Yuri,

                         

                        See below for brief explanation for Measures and Dims (I hope I am explaining them all):

                        • count (AS OF DATE): counts the number of days in a week the seller is present.  Max of 7, Min of 0.
                        • Seller Count (Present): counts if a seller was present in the current week.  If # of days the seller is present in a week is 4 or more then the seller is present.  If the # of days in a week is 3 or less the seller is not present.
                        • Prior Week Seller count?: counts if a seller was present (4 days or more) in the week just preceding.  This is a "trick" since the weeks are all aligned in numerical order.  I realize this would not work (I think) in a more structured db.
                        • Removed count: Simply counts or notes a "1" if the seller was present for the prior week and then not present in the current week.  This is a formula (in excel) that shows ("Seller Count (Present)" less "Prior Week Seller count?")
                        • Removed Sellers: this is what I am really trying to achieve and what I want shown on the viz. - the # of sellers removed (comparing this week vs. the prior week).

                        I think some of the above is duplicative - just trying to get to the # removed.

                         

                        Hope this makes sense.

                         

                        Thanks!

                        Denis