How to replicate sql logic of sum(case when ) in Quicksight

Question & Answer
, ,
1

Hi Team,

How can i replicate the following SQL login as a calculated field in Quicksight?

sum(
case
when <<$UserChoiceOfBenchmark>> = ‘TopNRevenue’ and title_name <> <<$ChosenTitle>> and wbr_region = <<$WBRregion>> and diff_weekly <= <<$DiffWeekly>> and rnk_rev <= <<$RankRevenue>> then revenue
when <<$UserChoiceOfBenchmark>> = ‘BenchmarkManual’ and title_name in (<<$BenchmarkTitles>>) and title_name <> <<$ChosenTitle>> and wbr_region = <<$WBRregion>> and diff_weekly <= <<$DiffWeekly>> then revenue
else 0
end) asBenchmarkRev

Currently I’m using dataset parameters with Direct Query, I want to switch to SPICE and replicate the same logic. Any leads are appreciated.

Thanks in advance!

2

Hi @Tanisha_Shetty,

as both cases are leading into revenue you can use OR to bring them together and use a sumif.

sumIf - Amazon QuickSight

For your “in” you need something like contains.

contains - Amazon QuickSight

To build something like

sumIf(revenue,(<<$UserChoiceOfBenchmark>> = ‘TopNRevenue’ AND title_name <> <<$ChosenTitle>> AND wbr_region = <<$WBRregion>> AND diff_weekly <= <<$DiffWeekly>> and rnk_rev <= <<$RankRevenue>>) OR
(<<$UserChoiceOfBenchmark>> = ‘BenchmarkManual’ AND contains(title_name,(<<$BenchmarkTitles>>))=TRUE AND title_name <> <<$ChosenTitle>> AND wbr_region = <<$WBRregion>> AND diff_weekly <= <<$DiffWeekly>>))

BR

3

Thanks @ErikG,

I solved it using sumif with OR condition which is inline with your suggestion

sumIf(revenue, (${UserChoiceOfBenchmark} = ‘TopNRevenue’ and {title_name} <> ${ChosenTitle} and {wbr_region} = ${WBRregion} and {diff_weekly} <= ${DiffWeekly} and {rnk_rev} <= ${RankRevenue})
OR
(${UserChoiceOfBenchmark} = ‘BenchmarkManual’ and {wbr_region} = ${WBRregion} and in({title_name}, ${BenchmarkManualTitles}) and {diff_weekly} <= ${DiffWeekly} and {rnk_rev} <= ${RankRevenue}))

I’ve used IN function instead of CONTAINS and it still works. Is it recommended to use CONTAINS over IN or is it ok?

Thanks,
Tanisha

4

You are right.
But on a short few i couldnt find a difference.

contains evaluates if the substring that you specify exists within an expression. If the expression contains the substring, contains returns true, and otherwise it returns false.

in evaluates if an expression exists within a literal list. If the list contains the expression, in returns true, and otherwise it returns false. in is case sensitive for string type inputs.

in accepts two kinds of literal list, one is manually entered list and the other is a multivalue parameter.

But if it works for you. Guess its fine.
Could you mark the post as solved then? Thx.

BR