Count if sum

[lg92]

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

[MayilVahanan · Accepted solution]

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)