Ifelse with calculated fields

July · April 30, 2024, 6:29pm

Hello everybody,

I need to create a calculated field to place the values in a table, but ifelse is not accepting it.

ifelse(
{journey} = ‘stage1’, {x_stage1},
{journey} = ‘stage2’, {x_stage2},
{journey} = ‘stage3’, {x_stage3},
{journey} = ‘stage4’, {x_stage4},
NULL
)

–calculated fields

stage1: ( {qt_stage1} / {qt_stage1} ) * 100
stage2: ( {qt_stage2} / {qt_stage1} ) * 100
stage3: ( {qt_stage3} / {qt_stage2} ) * 100
stage4: ( {qt_stage4} / {qt_stage3} ) * 100

How to proceed?

Thanks

How to proceed?

shravya · April 30, 2024, 7:33pm

Hi @July,

I believe you’re trying to create a calculated field using an if-else condition to determine which value to display based on the value of another field (journey ).

Sometimes referencing calculated fields within another calculated field can be challenging. I would suggest you to just give a try with the below formula and let me know if it worked.

ifelse(
{journey} = ‘stage1’, ({qt_stage1} / {qt_stage1}) * 100,
{journey} = ‘stage2’, ({qt_stage2} / {qt_stage1}) * 100,
{journey} = ‘stage3’, ({qt_stage3} / {qt_stage2}) * 100,
{journey} = ‘stage4’, ({qt_stage4} / {qt_stage3}) * 100,
NULL
)

Thank you,
Shravya

July · April 30, 2024, 7:38pm

Hi Shraya,

I got the same error, I can´t create a calculated field.

“Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination”

thomask · May 1, 2024, 7:23am

Hi,

this means, that you use an aggregation function like SUM or AVG in at least one of your fields.

You can try to use SUM(0) instead of NULL

Thomas

Sanjeeb2022 · May 1, 2024, 9:57am

Hi @July - When you are using if else condition, ensure the return type should of the same data type. In case if condition is returning integer type, then your else is also require to return integer. I guess @thomask suggestion should work for you.

See the details from QuickSight documentation.

Return type

ifelse returns a value of the same data type as the values in then-expression. All data returned then and else expressions must be of the same data type or be converted to the same data type.

Link - Ifelse - Amazon QuickSight

Regards - Sanjeeb

July · May 2, 2024, 1:05pm

Hi Thomask

I tried to use SUM(0) and I got the same error.

“Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination”

I can´t create the calculated field.

July · May 2, 2024, 1:06pm

Hi Sanjeeb2022

I tried to use SUM(0) and I got the same error.

“Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination”

I can´t create the calculated field.

ifelse(
{journey} = ‘stage1’, ({qt_stage1} / {qt_stage1}) * 100,
{journey} = ‘stage2’, ({qt_stage2} / {qt_stage1}) * 100,
{journey} = ‘stage3’, ({qt_stage3} / {qt_stage2}) * 100,
{journey} = ‘stage4’, ({qt_stage4} / {qt_stage3}) * 100,
sum(0)
)

or

ifelse(
{journey} = ‘stage1’, {x_stage1},
{journey} = ‘stage2’, {x_stage2},
{journey} = ‘stage3’, {x_stage3},
{journey} = ‘stage4’, {x_stage4},
sum(0)
)

ileites · May 2, 2024, 1:49pm

I don’t know your data, but you can try this

ifelse(
{journey} = ‘stage1’, (sum({qt_stage1}) /sum( {qt_stage1})) * 100,
{journey} = ‘stage2’, (sum({qt_stage2}) /sum( {qt_stage1})) * 100,
{journey} = ‘stage3’, (sum({qt_stage3}) /sum( {qt_stage2})) * 100,
{journey} = ‘stage4’, (sum({qt_stage4}) /sum( {qt_stage3})) * 100,
sum(0)
)

or

ifelse(
{journey} = ‘stage1’,sum( {x_stage1}),
{journey} = ‘stage2’,sum( {x_stage2}),
{journey} = ‘stage3’,sum({x_stage3}),
{journey} = ‘stage4’, sum({x_stage4}),
sum(0)
)

Sanjeeb2022 · May 2, 2024, 4:25pm

Hi @July - I think the data types are decimal in the if clause, can you try to put 0.0 in stead of 0 or simply default the value to 0.0

If it will not work, please share sample values so that we can replicate this at our end.

Note - Please do not share the data if it contains sensitive info.

Regards - Sanjeeb

July · May 2, 2024, 8:16pm

Hi Sanjeeb2022

I tried putting sum(0.0) instead of sum(0) but it’s not working either.

The calculation field cannot be created.

I sent the image. I can´t send the sample, but it would be it:

||—|—| |qt_customer|
|stage1|103.869|
|stage2|51.080|
|stage3|4.992|
|stage4|4.812|

I hope you can understand me.

Thank you

David_Wong · May 3, 2024, 2:00am

Hi @July,

It’s usually better if you can share a sample of your raw unaggregated data so we can better understand your problem. I’m guessing your raw data looks somewhat like this:

Can you try these calculated fields?

qt_stage1

distinctCountOver(ifelse(journey = 'stage1', {id_customer}, null), [], PRE_AGG)

qt_stage2

distinctCountOver(ifelse(journey = 'stage2', {id_customer}, null), [], PRE_AGG)

qt_stage3

distinctCountOver(ifelse(journey = 'stage3', {id_customer}, null), [], PRE_AGG)

qt_stage4

distinctCountOver(ifelse(journey = 'stage4', {id_customer}, null), [], PRE_AGG)

Percentage

ifelse(
    journey = 'stage1', {qt_stage1}/{qt_stage1},
    journey = 'stage2', {qt_stage2}/{qt_stage1},
    journey = 'stage3', {qt_stage3}/{qt_stage2},
    journey = 'stage4', {qt_stage4}/{qt_stage3},
    null
)

Add Percentage to your visual and set the aggregation to min, max or avg.

David_Wong · May 3, 2024, 2:11am

The reason you’re getting the mismatched aggregation error is because the condition in your ifelse is evaluated in every row of your dataset but your result (distinct_countIf) is aggregated. You can’t mix unaggregated and aggregated values in your calculated field.

The workaround is to use distinctCountOver with ifelse inside it. By using LAC-W, you can make the distinct count for each journey repeat in every row of your dataset like this:

You can then just do a simple division to get the percentages.

Sanjeeb2022 · May 3, 2024, 10:23am

Hi @July - The issue is calculated field and please try the suggestion provided by the @David_Wong , if it is not resolved, please share some sample mocked data.

Regards - Sanjeeb

July · May 3, 2024, 1:42pm

I’m almost there.

When I select a specific date the percentage is working correctly but when I select a month the same date appears different as the calculated field is like an average, the calculated field should be like ‘custom’.

I don’t want it to do the average but to show the exact value.

Aggregation with min or max doesn’t work either.

David_Wong · May 4, 2024, 10:29pm

If you want to filter by date, it gets more difficult. Try this:

qt_stage1

distinctCountOver(ifelse(journey = 'stage1', {id_customer}, null), [date], PRE_AGG)

qt_stage2

distinctCountOver(ifelse(journey = 'stage2', {id_customer}, null), [date], PRE_AGG)

qt_stage3

distinctCountOver(ifelse(journey = 'stage3', {id_customer}, null), [date], PRE_AGG)

qt_stage4

distinctCountOver(ifelse(journey = 'stage4', {id_customer}, null), [date], PRE_AGG)

Percentage

sum(
    min(
        ifelse(
            journey = 'stage1', {qt_stage1},
            journey = 'stage2', {qt_stage2},
            journey = 'stage3', {qt_stage3},
            journey = 'stage4', {qt_stage4},
            null
        ),
        [journey, date]
    )
) /
sum(
    min(
        ifelse(
            journey = 'stage1', {qt_stage1},
            journey = 'stage2', {qt_stage1},
            journey = 'stage3', {qt_stage2},
            journey = 'stage4', {qt_stage3},
            null
        ),
        [journey, date]
    )
)

If you can use SQL to do the first aggregation (counting the number of customers) at the dataset level, it will make things easier for you at the analysis level.

July · May 6, 2024, 2:55pm

Hi David_Wong!

Thank you very much!
It’s working!

I appreciated you attention.