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: 
lg92
Contributor II
Contributor II

Count if sum

Hi,

I have a problem with a complex count in following table

ID     Amount    Type
A        48312         10
A       348294       14
B         5849          12
C          58489       10
C           6859        12
D          68953      14
E          32490       11
F           8431        10
F           9650       14
G          4839       14
G      548239      10

 

I need to count (ID) following this step:

a) sum({<Type={'14'}>}Amount) / sum({<Type={'10'}>}Amount)

b)  IF  a) > 0  and  a) < 100 , count(ID) that respect this conditions

For example:  

ID = A ,  a) 348294/48312 = 7,2 ;  b) OK, count(ID) = 1

ID = B,   a) NO (not exist type=14 and type=10 for B)

ID = C,   a) NO (not exist type=14 for C)

....

The result will be count(ID) = 3  (A, F, G)

Thank you in advance

 

 

Labels (3)
1 Solution

Accepted Solutions
MayilVahanan

HI @lg92 

Try like below

=Count(Distinct {<ID = {"=sum({<Type={'14'}>}Amount) / sum({<Type={'10'}>}Amount)<100 and sum({<Type={'14'}>}Amount) / sum({<Type={'10'}>}Amount) > 0"}>}ID)

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

1 Reply
MayilVahanan

HI @lg92 

Try like below

=Count(Distinct {<ID = {"=sum({<Type={'14'}>}Amount) / sum({<Type={'10'}>}Amount)<100 and sum({<Type={'14'}>}Amount) / sum({<Type={'10'}>}Amount) > 0"}>}ID)

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.