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.
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>>))
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?
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.