Count distinct IDs if sum is between two values [Qlik Sense]

user_6224975 0
Viewed 768

Assume I have a customer database with their value of each basket, basket_value, and some customer ID's id. Based on the sum of each customer's baskes i.e sum(basket_value)i want to calculate how many customers there have a sum in interval [0-100], [100-200],[200,-].

I am fairly new to Qlik Sense and I have tried:

count({<id = {"=sum([basket_value]) <100"}>} distinct id) #interval 1
count({<id = {"=sum([basket_value])<200 and =sum([basket_value])>=100 "}>} distinct id) #interval 2
count({<id = {"=sum([basket_value]) >=200"}>} distinct id) #interval 3

which does not seem to give me the correct numbers.

What I want is just

COUNT(SUM({$<basket_value={"<200"}<}) distinct id) but nested aggreation is not allowed (for some reason)

qliksense
1 Answers

Try below

count({<id ={$(=sum([basket_value])<100)}>} distinct id)
or
Count({<id=p({<basket_value={"<100"}>}id)>}distinct id)

Thanks & Regards,
Prashant Sangle
https://predoole.com/

user_59015250
Related