6 Replies Latest reply on Jan 24, 2017 3:12 PM by Chris Warton

    Referencing Date Range Filter Values In Calculations

    Chris Warton

      We have users (named 1 User, 2 User, etc.) who have a certain number of product licenses assigned to them. On any given day, we can track how many of those licenses they use. We allow users to use more than their number of assigned licenses, and view those who've gone over as an upsell opportunity.

       

      So I've created a visualization that shows the percentage of their assigned licenses that users have used per day and put a filter on that percentage (License Usage Filter). I've also put a filter on the date range (Date Range Filter). You can see the results in the attachment.

      You can see that it only shows users who, at some time during the date range chosen on the Date Range Filter, have used between a percentage of their license count that is within the range of percentages chosen in the License Usage Filter, along with they days they did it and the percentage they used.

       

      But what I want to do is only show users who have used a percentage of their license count that falls into the range of percentages chosen in the License Usage on some chosen percentage of the days in the date range chosen on the Date Range Filter. So if the Date Range Filter covers a week and the percentage of days chosen is 50%, then only show the who have used a percentage of their license count that falls into the range of percentages chosen in the License Usage filter on at least 4 days of that week.

       

      I really don't know where to start.

       

      Thanks for any help.

        • 1. Re: Referencing Date Range Filter Values In Calculations
          Shinichiro Murakami

          Chris,

           

          Thank you for the detail explanation, but quite difficult to understand without thedata.

          Please attach packaged workbook. (**.TWBX)

           

          Tableau Forum Guidelines

           

          Thanks,

          Shin

          • 2. Re: Referencing Date Range Filter Values In Calculations
            Chris Warton

            Thanks, Shin - of course, you're right, I should have realised that; my apologies. Attached is a *.twbx I've worked up showing the problem.

             

            You can see the two filters - date (Date Range) and percentage range (Usage Range). It shows as a bar any days within the Date Range chosen where the usage percentage falls into the Usage Range chosen.

             

            But want I want it to do is:

            - allow the user to input a percentage (0%-100%) - call that the Threshold

            - only show the user where the number of days in Date Range that the user's usage was within the Usage Range as a percentage of the total number of days in Date Range exceeds the Threshold.

             

            The idea is that I can set a Threshold of (say) 50%, a Date Range of (say) the last week and a Usage Range of (say) >=100%. The only data that would show are those users who used more than 100% of their licensed usage on more than 50% of the days in the last week. I hope that makes sense.

            • 3. Re: Referencing Date Range Filter Values In Calculations
              Shinichiro Murakami

              HI Chris,

               

              I'm not sure you want to include "Date" of each user to calculate percentage or not.

              If you don't want, you can just simply remove date from the table.

               

               

              You can add parameter to allow users to select criteria.

               

              Create calc filed for filtering purpose.

              [Filter Percentage]

              [Usage Percent]>=[Percentage Param]

               

               

              Filter only True.

               

              Thanks,

              Shin

              • 4. Re: Referencing Date Range Filter Values In Calculations
                Chris Warton

                Hi Shin,

                 

                Thanks for your answer. Unfortunately it seems that my description of what I want isn't terribly clear - what you've created isn't what I need.

                 

                Thanks anyway.

                • 5. Re: Referencing Date Range Filter Values In Calculations
                  Shinichiro Murakami

                  Then, could you clarify your requirement?

                   

                  Thanks,

                  Shin

                  • 6. Re: Referencing Date Range Filter Values In Calculations
                    Chris Warton

                    I'll try to - I'll take it from a different angle, using a sort of bastardised pseudocode to see if that helps with understanding.

                     

                    Retrieve from Visualization:

                    - The range of dates chosen in the 'Date' filter (call this the 'Date_Range')

                    - The range of Usage Percent chosen in the 'AGG(Usage Percent)' filter (call this range 'Usage_Percent')

                    - The threshold value chosen in a new filter (this will be a means of the user choosing a percentage from 0% to 100%. This is an additional value; it is not a replacement for the 'AGG(Usage Percent)' filter) (call this value the 'Threshold_Value').

                    - Calculate the number of days in the Date Range (call this the 'Days_Number').

                    For each user in the database

                       Create a store called 'Days_Qualified' or zero it if it already exists.

                        For each date in the Date_Range

                           Calculate a percentage of usage for the user for that day from the number of usages over the number of licenses (call this 'Day_Percent_Used'.

                           If the Day_Percent_Used is within the Usage_Percent

                               Add 1 to Days_Qualified.

                           End if

                        End for

                       Calculate the Days_Qualified divided by the Days_Number as a percentage (call this value 'Qualified_Percentage').

                       If the Qualified_Percentage is >= the Threshold_Value

                           Display this User

                           For each date in the Date_Range

                               If the Day_Percent_Used for the day is within the Usage_Percent

                                   Display that day's usage in the chart.

                               End if

                           End for

                       End if

                    End for

                     

                    The idea here is that we already have a visualization that shows, for each user, all of the days chosen, whether they have exceeded a certain level of license usage (that's the visualization in the twbx I uploaded). But people want to know who are the ones doing it regularly - for example, who's done it more than 3 days in the last week? More than 10 days in the last month? That's the root use case behind this.