How to dynamically identify column with highest value per row in QuickSight

Q&A
, , , , , ,
1

Hi Team,
I have a data structured like this:

image
image740×247 7.17 KB

Requirement:

How can I dynamically calculate the most common response by identifying the column with the highest value per row in QuickSight?

image
image371×233 3.98 KB

Thanks,
Pradeep

2

Hi @bpradeepkumar

Please try the following calculated field using the ifelse logic, to dynamically identify the most common response.

Example:

ifelse(
    coalesce({Cycle One}, 0) >= coalesce({Cycle Two}, 0)
    AND coalesce({Cycle One}, 0) >= coalesce({Cycle Three}, 0)
    AND coalesce({Cycle One}, 0) >= coalesce({Not Needed}, 0)
    AND coalesce({Cycle One}, 0) >= coalesce(Flexible, 0),
    'Cycle One',

    coalesce({Cycle Two}, 0) >= coalesce({Cycle One}, 0)
    AND coalesce({Cycle Two}, 0) >= coalesce({Cycle Three}, 0)
    AND coalesce({Cycle Two}, 0) >= coalesce({Not Needed}, 0)
    AND coalesce({Cycle Two}, 0) >= coalesce(Flexible, 0),
    'Cycle Two',

    coalesce({Not Needed}, 0) >= coalesce({Cycle One}, 0)
    AND coalesce({Not Needed}, 0) >= coalesce({Cycle Two}, 0)
    AND coalesce({Not Needed}, 0) >= coalesce({Cycle Three}, 0)
    AND coalesce({Not Needed}, 0) >= coalesce(Flexible, 0),
    'Not Needed',

    coalesce({Cycle Three}, 0) >= coalesce({Cycle One}, 0)
    AND coalesce({Cycle Three}, 0) >= coalesce({Cycle Two}, 0)
    AND coalesce({Cycle Three}, 0) >= coalesce({Not Needed}, 0)
    AND coalesce({Cycle Three}, 0) >= coalesce(Flexible, 0),
    'Cycle Three',

    'Flexible'
)

image
image1187×372 38.1 KB

3

Hi @Xclipse,

Thanks for reply,

I want to clarify that I am working with a Pivot Table.

  • The column Hearsting contains values like:
    • Cycle One
    • Cycle Two
    • Cycle Three
    • Not Needed
    • Flexible
  • These are NOT separate columns in the dataset.

I am using a Pivot Table where:

  • Hearsting is placed in the Columns section
  • Group Name is placed in the Rows section
  • Response is placed in the Values section

Based on that need dynamically calculate the most common response by identifying the column with the highest value per row in QuickSight?

Thanks,
Pradeep

4

Hi @bpradeepkumar

Could you please share a sample of your dataset so I can better understand the structure?

5

Hi @Xclipse,

The following solution can be used to determine the Most Common Response

Create COUNT calculations each Response
Cycle_One =
countIf(Response = ‘Cycle One’)
Cycle_Two =
countIf(Response = ‘Cycle Two’)
Cycle_Three =
countIf(Response = ‘Cycle Three’)
Not_Needed =
countIf(Response = ‘Not Needed’)
Flexible =
countIf(Response = ‘Flexible’)

Most Common Response=
ifelse(
Cycle_One >= Cycle_Two
AND Cycle_One >= Cycle_Three
AND Cycle_One >= Not_Needed
AND Cycle_One >= Flexible,
‘Cycle One’,

Cycle_Two >= Cycle_One
AND Cycle_Two >= Cycle_Three
AND Cycle_Two >= Not_Needed
AND Cycle_Two >= Flexible,
'Cycle Two',

Cycle_Three >= Cycle_One
AND Cycle_Three >= Cycle_Two
AND Cycle_Three >= Not_Needed
AND Cycle_Three >= Flexible,
'Cycle Three',

Not_Needed >= Cycle_One
AND Not_Needed >= Cycle_Two
AND Not_Needed >= Cycle_Three
AND Not_Needed >= Flexible,
'Not Needed',

'Flexible'

)

Thanks,
Pradeep