topic Re: Count and Sum function in QlikView

Count and Sum function

deepakkrish — Wed, 25 Nov 2020 16:16:04 GMT

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 — Thu, 09 Jul 2020 10:43:23 GMT

Maye be :

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

deepakkrish — Thu, 09 Jul 2020 11:05:36 GMT

Thanks for the quick reply .

This is not working .. same results.

sunny_talwar — Thu, 09 Jul 2020 11:19:21 GMT

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 — Thu, 09 Jul 2020 12:01:49 GMT

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 — Thu, 09 Jul 2020 12:04:32 GMT

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 — Thu, 09 Jul 2020 12:11:48 GMT

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 — Thu, 09 Jul 2020 12:42:30 GMT

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 — Thu, 09 Jul 2020 13:02:21 GMT

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?