Hi, Tableau Fans
I have a question I call "dynamic numerical comparison". Here is the situation:
- I want to compare # of log-in vs. # of days for each supplier
- # of log-in is varied by the date range based on parameters "Start Date" and "End Date"
- # of days is also obviously varied by the date range based on parameters "Start Date" and "End Date"
- If supplier's # of log-in >= 50%* # of days based on given date range, then this supplier will be fallen into "category 1"
- If supplier's # of log-in <50% * # of days based on given date range, the this supplier will be fallen into "Category 2"
Currently, the challenge is that I don't want to explicitly show supplier but just number of suppliers fallen in each category, Tableau will automatically sum all suppliers' # of log-in together. I just want to compare on each individual supplier level. And I have tried LOD calculation and window_sum. But they haven't worked out as what I expect. The following is what I expect:
And I kinda of figure out a way to work around but it prevents me from building on next feature on dashboard. The following is the details of work-around:
- Created a calculated field for "# of days" = (DATEDIFF('day',[Start Date],[End Date])+1)
- Created a calculated field for comparision = IIF(sum([Log-in])>=1.3*AVG([# of days]), 'Category1','Category2')
- Created separated sheets, one for "Category1" and one for "Category2"
- I bring in supplier on dimension in order to do comparison on each individual supplier
- Then I hide the tile on supplier
- Count on supplier then turn to running_sum
- Just show the last records
Although this work-around can get me to what I want, it definitely cause a issue when I build action filter on graph. Because when I click on "category1" number 2, instead of showing line graph for aggregated these 2 suppliers' # log-in vs. # trade, it just shows the 2nd suppliers'.
I have attached my sample work on discussion forum.
Big thanks for all your help