3 Replies Latest reply on Jun 22, 2018 7:45 AM by Jim Dehner

    Drop off filter

    Christopher Scott

      We have yet to find a sufficient answer in the forums and even the Tableau help has been unsuccessful...so I'm reaching out to my favorite community!!!  I have a client that is looking to create a "Drop Off Filter" basically the gist is this: we want to see clients who have had sales in the past 8 weeks (more than 1 per week) and have 0 in the current week.  So they must have more than 1 every week for the past 8 weeks...if they miss one week they drop off.  We created multiple LOD calcs and the last solution we had was to create a calculation to check for EVERY week: if (DATETRUNC('week', [Date]) = DATEADD(DATETRUNC('week',[Date]),-8) then 1 end...and so on for every week.

       

      We were hoping there was a more logical way to do this without creating a SUPER long string of nested calcs or a super long calculated field to check for every week.

       

      Oh and we are using the equivalent of COUNTD([Order ID]) and that kind of throws a wrench in most of our calculated fields because it views this as a "non-aggregate"

       

      Any help would be great...thanks!

        • 1. Re: Drop off filter
          Mavis Liu

          Hi Christopher,

           

          Please could you provide a sample workbook or sample excel dataset?

           

          I have an idea of how to do this, but I'll need to see the data and review what you count as a 'sales', is there a column which indicates this? Or is it just when sales sum is over 0 for that week?

           

          Thanks,

           

          Mavis

          • 2. Re: Drop off filter
            Jay Morehart

            This is an interesting puzzle! I think I came up with something that should work. Basically you have 3 conditionals:

            None of the past 8 weeks have 0 orders

            This week has 0 Orders

            All of the past 8 weeks have more than 1 order

             

            This last condition appears to overlap the first, but I am assuming that your data is in a similar structure to Sample Superstore where there are no records for a week where there were no orders, so we have to handle it a bit differently. I came up with a solution that uses 3 LOD calculations (one for each condition) and only one of these is nested. Hopefully this will work with your data as well

             

            //check that there are no weeks without an order

            { FIXED [Sub-Category]: COUNTD(if abs(DATEDIFF('week',[Order Date WEEK],DATETRUNC('week',[Anchor Date]))) >=1

                                            then [Order Date WEEK]

                                            else null end)} = 8

            and

            //check that this week has no orders

            { FIXED [Sub-Category]: COUNTD(if abs(DATEDIFF('week',[Order Date WEEK],DATETRUNC('week',[Anchor Date]))) <1

                                            then [Order Date WEEK]

                                            else null end)} = 0

             

             

            and

            //check that each of the preceding 8 weeks have more than 1 order

            { FIXED [Sub-Category]:

                min(

                    {FIXED [Sub-Category],[Order Date WEEK]:

                       Countd(

                            [Order ID]

                        )

                    }

                )

            } > 1

             

            In sample superstore data I used [Sub-Category] instead of customer because there were no customers that had orders for 8 straight weeks.

            [Order Date WEEK] = DATETRUNC('week', [Order Date])

            (this might not be needed for the datediff calcs but it is needed for the nested LOD)

            [Anchor Date] is just a parameter I used because sample superstore only has data through Dec 2017 (for Tableau 10.4) this can be replaced with Now() for your purposes

             

            workbook attached in 10.4

             

            Hope this helps!

             

            --Jay

             

            EDIT: spelling, formatting

            • 3. Re: Drop off filter
              Jim Dehner

              Good morning - see the attached

               

              this is clunky - but I think it does what you want - 8 straight weeks of sales followed by none

              I had to use superstore data and used "subcategories" as a sub for customer (it was less messy)

              First had to fill in all the null sales records

              then an 1/0 sales indicator like yours

              then a running consecutive sales

              followed by filters

               

               

              it returns this

               

              I just flipped the axes to get this view

              Jim

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.