Is there any way to compare two table calculations made in Tableau to create a calculated field?

Question

I am relatively new to tableau! I am working on some problem which requires me to compare a table calculation to specified thresholds. I have five time windows namely 0-30, 30-60, 60-90, 90-120 and 120 onwards, to categorize my data into. This is spread across the data. I calculate the number of events which happen within certain time windows by doing '{FIXED [time window] : count([time window])}'. Thus I got a count for all the categories as 50 events happened which lasted 0-30s, 30 events lasted 30-60s, 10 events lasted for 60-90s and 5 events each for the rest of the two classes. I have a restriction of cumulative percentages as: 75, 90, 95, 97.5, 100.

I have created this using IF, ELSEIF and ELSE statements like:

IF time window = '0-30s' THEN 75
ELSEIF time window = '30-60s' THEN 90
ELSEIF time window = '60-90s' THEN 95
ELSEIF time window = '90-120s' THEN 97.5
ELSE 100.

and named this as specified cumulative share.

I make a table calculation for the obtained values as percent of total of Running total of the count of events in each class using primary and secondary table calculations for the measure and thus have got 50%, 80%, 90%, 95% and 100% for the respective classes. Now I need to compare each of them with the specified share and create another calculated field saying greater than, equal to or less than. How do I do it? The current table looks like this:

    **Time window** | **Obtained cumulative share** | **Specified cumulative share**
      0 - 30 s      |   50 %                        |   75
      30 - 60 s     |   80 %                        |   90
      60 - 90 s     |   90 %                        |   95
      90 - 120 s    |   95 %                        |   97.5 
      120 onwards   |   100 %                       |   100

    **Obtained cumulative share** is an alias for percent of total ( running total (counts for each 
    class))

It will be easier, if you could please upload some sample data in reproducible example stackoverflow.com/help/minimal-reproducible-example! — AnilGoyal
Your calc will be a little easier to read, type and maintain if you use a CASE statement. Logically equivalent though — Alex Blakemore

AnilGoyal AnilGoyal · Accepted Answer · 2020-10-20T09:00:47

I created a sample data and did it like this-

Instead of calculating cumulative sum through table calculation methood, use a function running_sum like this-

RUNNING_SUM(SUM([Count of Class] ))

I named this field as calculated cum sum.

create another calculated field for your T/F condition

MIN([specified Cum Share])>=([calculated cum share])

I have tweaked your specified shares just to check the formula is correct. See this view that it works.

Is there any way to compare two table calculations made in Tableau to create a calculated field?

1 Answers