10 Replies Latest reply on Mar 15, 2019 9:02 AM by Jim Dehner

    Consecutive Days Target Met w. Aggregate

    Corrie Martinson

      Hi Everyone,

       

      I'm building a viz based on Transaction Ratio or percent compared to calls (Transactions/Calls or sum(Transactions)/Sum(calls) for multiple days). The goal is to have 35% of our calls result in transactions per day. I've been asked to provide a standalone number at the top of the viz that shows the current number of days the business has hit that target. I'm having issues finding way to do this so it's just a standalone number. All the solutions I've tried, I usually end up with one of two problems. Either I remove the Date from the viz and that removes the consecutive date counts or the count totals the consecutive days and doesn't reset when the goal is missed.

       

      For instance, based on my current data, 3/12/2019 is our most recent date. Because we made the goal on the 11th and the 12th, my number in the Viz should be "2 consecutive days". If we made the goal on the 13th, once that's added to the data set, it would be "3 Consecutive Days". If we miss on the 14th, once that's added to the data set, we'd be back to 0 consecutive days.

       

      Attached is some test data in a twbx. I'm in version 10.5. I do know how to do this in the data itself, so if this requires something that is too "brute force" then I might just put this in the data. But I'm interested if Tableau can do this.

       

      Thanks!

        • 1. Re: Consecutive Days Target Met w. Aggregate
          Michel Caissie

          Corrie,

           

          Check in the attached,

          I made a copy of your calculation  Was Target Met,  but returning 1 or 0.

          Then I do a Window Sum to get the number you are looking for.

          Now, you have the number on every  Date row,  so i calculate a filter first() = 0  returning true only on the first row.

          You can see the result of those calculations in the sheet validateData.

           

          In the sheet finalView, I only keep Date on the Rows  but I right click the pill and unselect Show Header.

          And I put the filter  and keep the True value, which give you a single copy of the number in the view.

           

          Michel

           

          Edit: just realize , I miss the consecutives part of the requirements.

          looking at it....

          • 2. Re: Consecutive Days Target Met w. Aggregate
            Michel Caissie

            is this better ?

             

            you can use the previous_value function to compute the consecutiveness...

            if [Was Target Met? (copy)] = 0 then 0 else [Was Target Met? (copy)] + PREVIOUS_VALUE(0) end

             

            When its 3  flag the row with one

            if [Calculation1] = 3 then 1 else 0 end

             

            and WindowSum this last calc

            WINDOW_SUM( [Calculation2] )

             

            pay attention this last one is a nested calculation, you have to set the computing on both  nested calculation to Table Down

            • 3. Re: Consecutive Days Target Met w. Aggregate
              Jim Dehner

              see the attached

              and yes this is definitely brute force

              it is well down the list of nested table calculations

              this just places a counter on was met

              this one is tricky it adds UP the consecutive Met days

              then because you need to count down we reverse the calc

              just calc down

              this will get the last day

               

              the max just accounts for a ) on the last day - this would return a -1 if it wasn't there

               

               

              create the viz

              now you need to hide a lot of things

              and you end up with this

               

               

              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.

              • 4. Re: Consecutive Days Target Met w. Aggregate
                Corrie Martinson

                Hi Michel,

                 

                It looks like this is returning the wrong answer - it says 5, but the consecutive days should be 2. It looks like Jim has a solution, but it looks pretty intense... Thank you for the help!

                • 5. Re: Consecutive Days Target Met w. Aggregate
                  Corrie Martinson

                  Thanks Jim! I saw you post this in a different thread, but I couldn't quite get it to work for me. I think at the point I was at when I was trying it, I was still using a sum on transactions and calls to get the transacted ratio and by the time I realized that for one day I could get that number unaggregated i was pretty fed up. This is extremely brute force, so I might just add it to my dataset. I am a data dev by trade, so I have full control over the view on my server.

                  • 6. Re: Consecutive Days Target Met w. Aggregate
                    Jim Dehner

                    Thanks Corrie

                    this type of problem is frustrating because there is not single calculation that will get you there - I have 2 others posted on my Public page - one is breaking customers into sale quintiles - the other is counting consecutive days then ranking them to find the 3 longest string - If you don't mind I think I will add this -   Tableau Public

                     

                    Jim

                    • 7. Re: Consecutive Days Target Met w. Aggregate
                      Corrie Martinson

                      It is frustrating, Tableau is so powerful sometimes the simple things take a lot to get the result. I put this in the SQL - I basically took the max date of the last miss and did a date diff on the total max date and cross joined it back to my query. We're only interested in the current consecutive days, so this repeats the number in the dataset, but that's fine. Not a very complex solution to get it added to the data at least

                      • 8. Re: Consecutive Days Target Met w. Aggregate
                        Jim Dehner

                        but SQL doesn't handle mapping very well

                        Jim

                        • 9. Re: Consecutive Days Target Met w. Aggregate
                          Michel Caissie

                          Corrie,

                           

                          That's what happens when I try to rush a question before leaving the office, I misread the requirements.

                          5 is the number of times you had at least 3 consecutive days meeting the criteria.

                           

                          Yes Tableau can be frustrating sometimes, and having control on the database through custom queries, store proc or views is surely a plus. This way you can distribute your logic between Tableau and SQL.

                          But learning to use Tableau's table calculations, and lod calculations is also a big plus .  It took Jim less than 45 minutes to find the solution.

                          • 10. Re: Consecutive Days Target Met w. Aggregate
                            Jim Dehner

                            Hi Michael

                            I had done something similar last week - and that one took a lot longer than 45 min

                            Have a good weekend

                            Jim