Tableau's functions - how to find an equivalent to IF EXISTS

Question

I'm creating a Tableau Dashboard with 'buttons' which are coloured red or green based on certain criteria and what is selected in the filters. The filters are just a way to select different offices in different regions and when selecting an office the buttons should change colour depending on whether the targets for the different metrics have been hit for that office or not.

The navigation buttons on Tableau won't accommodate this so I've made a work around. For each 'button' I've created a worksheet with just the text of the metric name on the Label mark and a calculated field on the colour mark. I've then added the worksheet to the Dashboard and added an action to go to the corresponding metric dashboard when the 'button' is clicked on.

The issue I'm having is the conditional colouring of one of these metrics. This metric is based on stock levels. For each office there are multiple categories of stock types, each with a corresponding target, with multiple 'bins' in each category. I want the button to turn red if ANY of the combined total of stock in the bins for one category is over the target for that category for that office.

To try and type it logically- For the currently filtered data: IF EXISTS(FOR EACH OFFICE( FOR EACH CATEGORY: [SUM(BinValue)< CategoryTarget])) THEN 'Green' ELSE 'Red'

I've tried to translate that logic into Tableau's functions in a calculated field and have the following: SUM(INT({INCLUDE [Category]:Min([CategoryTarget])} > {INCLUDE [Category]:SUM(BinValue)}))

This colouring is correct when I add the Office Name and Category pills to the worksheet to test my logic however when I remove the pills the colouring isn't correct. Something seems to be going wrong when I try to sum the number of categories that are within target levels over all offices and targets.

I've tried so many iterations of the following functions and have been going around in circles for days now: INCLUDE, EXCLUDE, FIXED, IF, SUM, INT

If anyone knows how to do this properly or even just a different way of being able to conditionally colour buttons on a dashboard I would be incredibly grateful.

The structure of my data is as follows with some dummy data as an example:

Region	SubRegion	Office	Category	Bin	BinValue	CategoryTarget
North	NorthWest	Manchester	Toys	B123	30	50
North	NorthWest	Manchester	Toys	B456	40	50

So for a Stock Level metric selecting any of ALL/North/NorthWest/Manchester filter options should flag as red due to the total of the bins in one category in an office being higher than the target amount for that category for that office.

I've updated my calculated field however I'm still having issues with the grouping showing as true/false correctly. This is what it is now: MAX( {INCLUDE Category, Office:Sum(BinValue)} > {INCLUDE Category, Office:MIN(CategoryTarget)} ) With True showing as Red and False Green (we want to be below target hence the green).

Answer 1

When working on the example to showcase the issue I managed to get it working.

I ended up using the following logic: max({EXCLUDE [Bin]:SUM([Bin Value])} > [Category Target])

This meant that even if most of the Offices in the filter were within their stock level targets, if there was one with stock levels over target the 'button' showed as red.

I published the example I've used anyway in case it helps others in the future. Link to the Tableau Public dashboard: https://public.tableau.com/views/ConditionalColouring/Dashboard1?:language=en-GB&:useGuest=true&:display_count=y&:origin=viz_share_link

Thank you very much for the help!

Answer 2

To work with logical conditions, such as testing whether a condition holds for any (or every) record in a group of data rows, it helps to understand that Tableau treats the boolean value "True" as greater than the boolean value "False".

Once you get comfortable with that idea, you can use the functions MAX() (or MIN()) to test whether a condition holds for any record (or for every record, respectively). So MAX(False, False, True, False) is True.

So to tell if any records have an actual value below their target, test MAX([Actual Value] < [Target Value])

You can then combine this idea with dimensions on the viz (or LOD calcs if necessary) to group the data records appropriately before testing your conditions. If you work with the same conditions repeatedly, this type of calculation can be very useful for defining sets that get used in multiple places.

One technical caveat, if your condition test ever evaluates to NULL, then those null values are ignored by MIN() and MAX() - just like other aggregation functions do. So for example, you could test whether every record satisfies a condition using MIN() and get a possibly misleading result if all the non-null values are True (so MIN() reports True). MIN(TRUE, TRUE, NULL, TRUE) = TRUE. If your condition can evaluate to NULL, and you don't want to ignore nulls, but instead treat it as, say, the same as False, then you can use the IFNULL() function to provide a default value for your condition.

As an example, MIN(IFNULL([Actual Value] > [Target Value], FALSE)) returns True only if every record has a value above its target, treating any records with missing values or targets as failing the condition - i.e. not exceeding the target. The choice of whether to have a default value for a condition, and what it should be, are problem dependent of course. If your data does not have null values, you don't have this complication to consider.

Answer 3

Though the data you have given is very less, yet I think this calculation field you require

IF { FIXED [Region], [Sub-Region], [Office], [Category] : SUM([Bin Value])}
> {FIXED [Region], [Sub-Region], [Office], [Category] : MIN([Category Target])} THEN 'RED' ELSE 'GREEN' END

This is based on assumption that for every group of region/sub-region/office/category target value will be same in each row within the group. Therefore MAX/AVG etc. will all work in place of MIN used in the calculation.

See I added two rows in your data

and result