Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Count distinct if Sum > amount

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!

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Creator
Creator

Re: Count distinct if Sum > amount

Hi,
Can you try this..

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

View solution in original post

4 Replies
Highlighted
Contributor III
Contributor III

Re: Count distinct if Sum > amount

please provide details about the dimension used

Contributor III
Contributor III

Re: Count distinct if Sum > amount

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 ?

Highlighted
Creator
Creator

Re: Count distinct if Sum > amount

Hi,
Can you try this..

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

View solution in original post

Highlighted
Contributor III
Contributor III

Re: Count distinct if Sum > amount

Yes!  This worked great!   Thanks for your help!