Calculated field to sum based on multiple filters

Question & Answer
, , , ,
1

Hi!

I need help with a request that I’ve been stuck on and that’s been making me rack my brains for days. I’m replicating a dashboard that exists in Excel for Quicksight.

I have a column “Value 1” and “Value 2”

In dashboard control, there are 5 filters: Category, Type, Source, Month and Region. I’m trying to create a calculated field for “Value 1” that will be manipulated according to the Category, Type and Source filters.

In other words, the month and region filter will impact both “Value 1” and “Value 2”, and the remaining filters will only impact “Value 1”.

One way I did it was through the parameters, applying a sequence of if else. At first, it’s working, but if I have to check the conditions of more filters in the calculated field, the code becomes extremely large, confusing and very difficult to maintain.

Is there a way to do what I’m looking for in a simpler way? I don’t know if my explanation was clear.

I tried using the contains() function, but as my filter is multiple selection, it doesn’t work.

2

Hi @martinsfgr,

Would you be able to show your calculated fields for “Value 1” and “Value 2”?

3

Here’s an example, using just the “Category” and “Type” filters. For the Category, Type and Source filters, I used parameters. For month and region, I used the default filter

ifelse(
isNull(${Type}),
ifelse(
isNull(${Category}),
{Value_1},
ifelse(
${Category}=Category,
{Value_1},
0
)
),
ifelse(
isNull(${Category}),
ifelse(
${Type}={Type},
{Value_1},
0
),
ifelse(
${Type}={Type} AND ${Category}=Category,
{Value_1},
0
)
)
)

4

Hi @martinsfgr,

You don’t need to nest multiple ifelse functions like that. That’s the main difference between ifelse in QuickSight and the IF function in Excel. In QuickSight, you can put all your conditions in the same ifelse function like this:

ifelse(
    {fieldA} = ${fieldA}, {Value1},
    {fieldB} = ${fieldB}, {Value2},
    null
)

Can you rewrite your calculated field using this syntax?

5

Hi @martinsfgr,
It’s been awhile since we last heard from you, did you have any further questions or did the solution provided above work for your case?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

6

Hi @martinsfgr,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for any relevant information that may be needed.

Thank you!