5 Replies Latest reply on Aug 21, 2018 12:53 PM by Sheng Zhong

Dynamic Numerical Comparison Question

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:

1. Created a calculated field for "# of days" = (DATEDIFF('day',[Start Date],[End Date])+1)
2. Created a calculated field for comparision = IIF(sum([Log-in])>=1.3*AVG([# of days]), 'Category1','Category2')
3. Created separated sheets, one for "Category1" and one for "Category2"
4. I bring in supplier on dimension in order to do comparison on each individual supplier
5. Then I hide the tile on supplier
6. Count on supplier then turn to running_sum
7. 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

• 1. Re: Dynamic Numerical Comparison Question

And this is what I did:

1. Created a calculated field for # of logins: {FIXED [Supplier]:SUM([Log-in])}

2. Created a calculated field for Category 1: { FIXED [Supplier]:SUM(If [# of Logins]>=1.3*[# of days] THEN 1 ELSE 0 END)}

3. Created a calculated field for Category 2: { FIXED [Supplier]:SUM(If [# of Logins]<1.3*[# of days] THEN 1 ELSE 0 END)}

4. Added "Within Date" calculated field to filter and selected "True" and added it to context.

Note: I have used 1.3 above instead of 1.5 so that we see both category values.

Hope this helps.

• 2. Re: Dynamic Numerical Comparison Question

Hi Sheng,

One way to make the line graph responsive to action filters is to use an LOD approach.

Step 1: Create calculated field [Log-in (LOD)]

{FIXED [Supplier]: SUM([Log-in])}

Step 2: Create calculated field [# of days (LOD)]

{FIXED [Supplier]: MIN((DATEDIFF('day',[Start Date],[End Date])+1))}

Step 3: Create calculated field [Comparison (LOD)]

IF [Log-in (LOD)] >= 1.3 * [# of days (LOD)] THEN 'Category1' ELSE 'Category2' END

Step 4: Create calculated field [Comparison Filter]

LOOKUP(MIN([Comparison (LOD)]),0)

Add [Within Date] filter to context in all the worksheets. Add [Comparison Filter] to the filter shelf of 'Work-around for Category 1' and 'Work-around for Category 2' worksheets.

Add [Comparison (LOD)] to the Details marks card of 'Work-around for Category 1' and 'Work-around for Category 2' worksheets.

Step 6: Set up the action filter like below.

Below are the results.

Hope this helps.

Ossai

1 of 1 people found this helpful
• 3. Re: Dynamic Numerical Comparison Question

Hi, Hari

Thanks very much for your help!

• 4. Re: Dynamic Numerical Comparison Question

You are welcome.

• 5. Re: Dynamic Numerical Comparison Question

Hi, Okechukwu

I very appreciate for your help and detailed information. But I have a few questions to ask for better understanding LOD:

1. Is it necessary to use fixed LOD to calculate "# of days"?
2. Why have to change the calculated field "within day" to context filter? just because we need to filter on first?
3. what is rationale for [Comparison Filter]=LOOKUP(MIN([Comparison (LOD)]),0)
4. What is rationale for putting the calculated filed in details mark.

What if I introduce another data source (primary data source) and blend with the existing one and treat the existing one as secondary data source, how can I achieve the same action filter functionality by click on either number to filter on data on another data source?