topic Re: Count when the sum is greater than a variable in QlikView

Count when the sum is greater than a variable

leenlart — Sat, 16 Nov 2024 17:41:33 GMT

Hello,

I'm trying to count clients that have made purchases over a target amount. The target amount is in a variable: vINC1_VOL_MIN.

I've whittled down my expression to the following , but it is returning a count for any client that has ordered any amount :

count( aggr( sum({$<TYPE_CALENDRIER={'N'},ORIGIN={'FACTURES'}>} [TONNAGE VENDU]), %CLIENT_ID)>=$(vINC1_VOL_MIN) )

My control table looks like this, as you can see the first column (the formula above) is returning a 1 for all lines, regardless of the sum of sales.

Where do I need to put the condition so that the count takes it into ... account ?

Thanks so much for your help!!

Re: Count when the sum is greater than a variable

lfholland — Thu, 17 Dec 2020 20:41:59 GMT

Try this:

count({<TYPE_CALENDRIER={'N'},ORIGIN={'FACTURES'}>}
distinct if(
aggr(sum({<TYPE_CALENDRIER={'N'},ORIGIN={'FACTURES'}>} [TONNAGE VENDU]), %CLIENT_ID)>=$(vINC1_VOL_MIN),
%CLIENT_ID))

Re: Count when the sum is greater than a variable

leenlart — Fri, 18 Dec 2020 08:54:33 GMT

Brilliant!

Thanks so much for your help!