Count and Sum function

[deepakkrish]

Hi,

I am trying to write an expression where I want to calculate the number of ID's with Amount less than or equal to 500.

Count({$<Amount={"$(='<=500')"},
Status -= {'Lost'}, Stage = {'Booked', 'promised'}>}ID).

it should not check the ID line by line. so,the Amount should be sum up , Sum(amount)<=500.

I'd appreciate if I could get the best method/syntax to produce this result?

Thank you in advance.
Deepak.

[Taoufiq_Zarra]

Maye be :

=Count({$<ID={"=Sum(amount)<=500"},
Status -= {'Lost'}, Stage = {'Booked', 'promised'}>}ID)

[deepakkrish]

Thanks for the quick reply .

This is not working .. same results.

[sunny_talwar]

Qlik is case sensitive, did you try to use Amount instead of amount?

=Count({$<ID = {"=Sum(Amount) <= 500"}, Status -= {'Lost'}, Stage = {'Booked', 'promised'}>} ID)

[deepakkrish]

yes ., Seems its working now.. 

Can you please guide me how to apply the same expression function in Straight table.

=if(match(Stage,'Promised', 'Booked') and (match(Status, 'Lost') = 0) , if(sum(Amount)<=500 , ID))

Thanks,

Deepak.

[deepakkrish]

I have to apply this in calculated dimension.

=if(match(Stage,'Promised', 'Booked') and (match(Status, 'Lost') = 0) , if(sum(Amount)<=500 , ID))

Regards,

Deepak.

[Taoufiq_Zarra · Accepted solution]

Maye be :

=if(match(Stage,'Promised', 'Booked') and (match(Status, 'Lost') = 0) , if(
aggr(sum(Amount),ID)<=500,ID))

and check Supress when value is Null

[deepakkrish]

Thank you Zaara . It's perfectly working.

If you don't mind , Can you please let me know how to apply the same in script side. with yes or no condition.

if(match(Stage,'Promised', 'Booked') and (match(Status, 'Lost') = 0) , if(
aggr(sum(Amount),ID)<=500,ID),'Yes','No')

[Taoufiq_Zarra]

@deepakkrish 

if(match(Stage,'Promised', 'Booked') and (match(Status, 'Lost') = 0) , if(
aggr(sum(Amount),ID)<=500,ID),'Yes','No') is correct

Do you mean in Load Script ? If yes, can you share a sample data?