Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
deepakkrish
Creator
Creator

Count and Sum function

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.

1 Solution

Accepted Solutions
Taoufiq_Zarra

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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

8 Replies
Taoufiq_Zarra

Maye be :

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

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
deepakkrish
Creator
Creator
Author

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
Creator
Creator
Author

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
Creator
Creator
Author

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

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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
deepakkrish
Creator
Creator
Author

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?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉