topic Re: Count distinct if Sum > amount in QlikView

Count distinct if Sum > amount

leenlart — Sat, 16 Nov 2024 04:20:58 GMT

Hello,

I have a table of lines of invoices (invoice number - date - client - item - quantity).

I need to count the invoice numbers where the total amount ordered for all items is below 10 kg. The count will be shown in a table by client.

CHRONOFA = the invoice number

TONNAGE VENDU = quantity

I've tried :

=count(distinct aggr ( if (sum([TONNAGE VENDU])<10, CHRONOFA) , CHRONOFA))

but its not giving me the right number.

I've also tried :

=Count(DISTINCT {<CHRONOFA = {"=Sum([Tonnage_Vendu]) < 10"}>} CHRONOFA)

Any ideas anyone??

Thanks!

sivakumar1994 — Mon, 25 Feb 2019 15:55:51 GMT

please provide details about the dimension used

leenlart — Mon, 25 Feb 2019 16:03:49 GMT

Per client, I want to count the distinct invoice numbers (field CHRONOFA) when the total sum of the quantity (field [TONNAGE VENDU]) is under 10.

I'm not sure I'm actually replying to your question... could you be more specific in what details you are interested in ?

santhiqlik — Mon, 25 Feb 2019 18:34:36 GMT

Hi,
Can you try this..

=Sum(IF(Aggr(sum([TONNAGE VENDU]),CHRONOFA) < 10,1))

leenlart — Tue, 26 Feb 2019 08:29:27 GMT

Yes! This worked great! Thanks for your help!