Hi all,
I would like to create a sort of comparator in quicksight.
The user should be able to pick a baseline group and a comparison group by selecting the features of each group from some controls inserted in the page (see picture attached).
The only solution I found so far is to define two calculated field base and comparison as:
comparison ifelse(in({trans_cpp_size_category}, ${sizeBase}),“base”,“null”)
comparison ifelse(in({trans_cpp_size_category}, ${sizeComparison}),“comparison”,“null”)
by using two different parameters sizeBase and sizeComparison.
Question 1.
Is there a way to create a unique field “grouping” which can be base or comparison, able to handle the situation in cui the groups are the same?
i.e this solution is not working great
grouping
ifelse(in({trans_cpp_size_category}, ${sizeBase}),“base”,
in({trans_cpp_size_category}, ${sizeComparison}),“comparison”,
“null”)
in case sizeBase and sizeComparison are both Small, I’m not entering in the else part of the conditional expression, therefore comparison will be null.
Question 2.
For realizing the diff column I would like to avoid to manually doing the diff of each metric
i.e.
tot_quantity_diff
sumIf({tot_quantity},base=‘base’)-sumIf({tot_quantity},comparison=‘comparison’)
In this way I’m getting the difference between base and comparison but I would need to potentially create 40-50 different calculated field for each metric…
Happy to clarify further if someone can help 