Calculate and display percentage of a boolean column, and date ranges across a set of columns

Question

This is what a few rows looks like in my dataset in Tableau

Country	State	Date	Success
INDIA	MH	2023/01/01	True
INDIA	MH	2023/01/02	False
INDIA	KA	2023/01/02	False
USA	OR	2023/01/01	True
USA	NY	2023/01/15	False

I want to create a Tableau dashboard that shows a success ratio for a given Country, State and Year. Year is a filter that the user can choose. I also want to show a column that shows date ranges of country and state for a given year, where success was false. Something like

Country	State	Success Ratio	Failure dates
INDIA	MH	73%	01/15-03/20, 04/01-04/15, 05/07, 06/08
INDIA	KA	73%	02/15-03/25, 04/01, 04/25-05/07, 06/09
US	WA	25%	05/25-08/28, 09/12
US	NY	48%	11/01 - 12/20, 12,24

Success ratio is defined as percentage of success datapoints over total datapoints for that country and state in that year. I'm using Tableau Desktop, and very new to it

Answer 1

You can use WITH to create two tables (one with success ratio and one with failure dates) and join these to get the desired result. This can be a view tableau looks at. example below

with a as  (
select country , state, STRING_AGG(Date, ', ') as [Failure] from 
YourTable where success = 'false'
group by country , state
),
b as (
select country 
, state
,  (sum (case when success = 'true' then 1 else 0 end)/ cast(count(1) as 
decimal(3,2)))* 100  as [Ratio]
from YourTable
group by country , state
)
select b.*, a.Failure from b left join a on a.country = b.country and a.State = b.State

Answer 2

You can define a calculated field called Success_Ratio as

SUM(INT([SUCCESS])) / COUNT([My Input Table Name])

or equivalently

SUM(INT([SUCCESS])) / COUNT("*")

Then set the default number format for your new field to be a percentage (i.e. right click on the field in the data pane left margin and choose Default Properties->Number Format)

This works because the type conversion function INT() maps True to one and False to zero.

The easiest way to show the failure dates is with another viz or table, either using a filter action on a dashboard to let people drill down to a list of dates, or using a viz in a tooltip. There are ways to rollup a comma separated list like you showed, but they take more work than simply using a separated viz to drill from down a selected state to show a list of failure dates, each on its own row.