Hello everybody,
Hopefully you can help me with a question regarding a sums over multible aggregations.
I have users divided into two groups. Every user has purchases per date. What I want to know is for every person in group 1 the number of people in group 2, which have less purchases (in the selected time). The result of this should then be summed up and put into a textbox.
I tried to solve the problem differently, but without any success.
My first attemt was, to compare to aggregated dimensions:
sum(if(
aggr(sum({$<group = {1}>}purchases), user_id) > aggr(sum({$<group = {2}>}purchases), user_id)
,1))
Then I thought, maybe I have to load everything twice, to make Qlikview sum over all combinations:
sum(if(
aggr(sum({$<group = {1}>}purchases), user_id) > aggr(sum({$<group_2 = {2}>}purchases_2), user_id_2)
,1))
My third option was, to try to rank everything and just add the right ranks
sum(
aggr(
count(below(rank(
aggr(sum({$<group = {2}>} purchases), user_id)))),
if(group = 1, user_id)
))
I also tried different combination, but unfortunately all attempts were without the right outcome. Do you guys have any clue, how to accomplish an aggregation over all combinations of two dimensions (or two times the same dimension)?
Thanks a lot in advance.
Regards
Ulrich
