I am creating a calculated field within an analysis. This calculated field uses an ifelse statement to say if sum(field X) >= 20, then 1, else 0. This new field then appears with “(Custom)” next to it when I drag it into Values in Field Wells. I have several other “(Custom)” calculated fields as well.
I would like to show KPI’s for these fields such as sum and average. However, when I drag these variables into a KPI, I am not given any aggregate options other than “Custom”. And the KPI itself is just a 0 or 1. Whereas when I look at a KPI for a variable that says “(Sum)”, I am able to choose between average, count, median, etc, and get actual KPI values.
@connoranderson can you provide some more detail on what you are trying to do in business terms? Wondering if you really need the sum inside your calculated field. Also trying to understand what you want to do with those 1’s after. Are you trying to do something like ‘count how many customers who have spent more than $20’? in a KPI?
No problem. So we’re looking at minutes. Specifically how many minutes our staff have spent with each patient working on 3 different task types. I have 3 pivot tables for each of the 3 task types with patients as rows. The first column is “Duration_Minutes” which comes from our dataset. Within the pivot, this column shows for each patient the sum of their total minutes associated with that task type. Then I would like a second column that uses my ifelse statement above to say if Duration_Minutes >= 20, then give a 1, else give a 0.
You are right, I didn’t think I would need to use a sum within the ifelse either, but when I execute that formula without the sum I can see that many patients who have over 20 in Duration_Minutes are not given a 1. I think this is because it’s looking at rows in our dataset to see if the patient has at least 1 record where Duration_Minutes is >= to 20, not looking at the summed value under Duration_Minutes in the pivot.
To your last question, yes ultimately I’d like to count up the 1’s resulting from that ifelse to say how many patients have at least 20 minutes for the 3 task types.
Custom Calculated Field
QuickSight applies the “Custom” label to Aggregate functions. You’ve already specified the aggregation for the visual in your calculated field and the UI will use that.
It would look something like this for your use case.
Use a LAC-A to sum the minutes at the same level as your group by.
c_visit_duration_LAC-A
sum(Duration_Minutes,[{visit_id}])
You can use this calculated field in your visual for further aggregation because LAC-A and LAC-W functions are performed before your visual aggregation (order of evaluation)