Hello, we want to compare a ratio of a certain instance in a country to the benchmark of the country in a quicksight dashboard. To understand the situation, I created an example table:
parent
child
country_code
sells
costs
sell cost ratio per parent and country (parent/country benchmark)
avg sell cost ratio per country (country benchmark)
compare sell cost ratio for child to country benchmark
A
1
GE
129
563
0.40
0.38
0.60
A
2
GE
230
343
0.40
0.38
1.75
B
3
GE
289
646
0.37
0.38
1.17
B
4
GE
262
846
0.37
0.38
0.81
C
5
AU
9456
4525
2.07
1.57
1.33
C
6
AU
0
43
2.07
1.57
0.00
D
7
AU
543
545
1.08
1.57
0.63
D
8
AU
643
556
1.08
1.57
0.73
In the table you see multiple parents and child instances in different countries and their sell and costs. What we want to do first is calculate the sell cost ratio (sell/cost) for each advertiser and country. Next we want to calculate with that the country benchmark (avg). In the last step, we want to compare the sell cost ratio from each child to the country benchmark we just calculated.
Thereby, we annonymise the child numbers in the benchmark by having the avg of the avg.
Unfortunatey, we were not able to calculate that inside quicksight with fields. We assumed we need a PRE_FILTER function to calculate the benchmark and than compare it to each child, but it was not possible and destroyed the numbers. Maybe somebody of you can help us. Thanks
Not sure if I understand your complete use case. But here is your example table replicated in QuickSight/Arena. You can hit “copy analysis” on the left side pane to check out the calculated fields.
thanks for your help! I apologize that I forgot one important aspect: we now don’t want to show the entire table, but maybe just one parent. I attached a screenshot from your analysis when you filter for one parent. Because of the filtering, it changes the calculations.
Nevertheless, I would like to get the same result in c_compare as you have, just don’t show all of the parents, but maybe just one. My expected result should be:
parent
child
country_code
sells
costs
sell cost ratio per parent and country (parent/country benchmark)
avg sell cost ratio per country (country benchmark)
compare sell cost ratio for child to country benchmark
A
1
GE
129
563
0.40
0.38
0.60
A
2
GE
230
343
0.40
0.38
1.75
I think that is a use case for the PRE_FILTER function, but we cannot make it work. Thanks for the help here!
Hi Ali,
thanks for the feedback. I would like to show the comparison between each child and the benchmark for the country. Therefore, I need the child.
