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.
Sample.twbx 28.5 KB
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.
[Usage Percent]>=[Percentage Param]
Filter only True.
Sample_Parameter_SM_10.1.twbx 49.4 KB
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.
Then, could you clarify your requirement?
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.
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.
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.