Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sagarjagga
Creator
Creator

sum and aggregation in count in set analysis of qliksense chart

Hi,

I need to count the ids whose sum is greater than 3. let me explain with an example. Assume i have below table.

      

managerwholesellersupervisiorIddateTransaction Amt
sagarrahulram11/20/20161
sagarrahulram11/7/20162
sagarrahulram11/8/20175
sagarrahulram29/5/20168
sagarrahulram39/5/20187
AnindyavikasDeb104/8/20164
AnindyavikasFiroz45/4/20169

under the manager there are some wholeseller and under the wholersellers there are some supervisiors and further under the supervisior there are some ids those ids have some transaction on particular dates with some transaction amt. i need to find out the count of ids whose transaction amt is greater than equal to 3 in lets suppose in year 2016. My final table will look like after considering the condition

    

managerwholesellersupervisiorCount
SagarRahulram2(as id 1 has sum=3,id 2 has sum = 8, 3 has 0 sale in year=2016)
AnindyavikasDeb1(id 10 has sum =4)
AnindyavikasFiroz1(id 4 has sum = 9)
3 Replies
sunny_talwar

Try this

=Count(DISTINCT {<Id = {"=Sum({<year = {2016}>}[Transaction Amt]) >= 3"}, year = {2016}>} Id)

sagarjagga
Creator
Creator
Author

Thank you sunny, However in case of same ids present under different supervisiors , is giving problem, for example for below table if i use Transaction Amt >= 6 without using date dimension for now


managerwholesellersupervisiorIddateTransaction Amt
sagarrahulram11/20/20161
sagarrahulram11/7/20162
sagarrahulram11/8/20175
sagarrahulram29/5/20168
sagarrahulram39/5/20187
AnindyavikasDeb24/8/20164
AnindyavikasFiroz45/4/20169


it will give me below output after using Transaction Amt >= 6 without considering the date for now.

managerwholesellersupervisiorCount
SagarRahulram2(as id 1 has sum=8,id 2 has sum = 8, 3 has 7 over sale)
AnindyavikasDeb1(id 10 has sum =4 but still giving me count)
AnindyavikasFiroz1(id 4 has sum = 9)


Supervisior deb is still giving me a count as it is considering the id 1 although it's sum is 4 which is less than 6


sunny_talwar

May be you need this then

=Count({<year = {2016}>}DISTINCT Aggr(If(Sum({<year = {2016}>}[Transaction Amt]) >= 6, Id), Id, supervisior))