Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
leenlart
Creator
Creator

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

Hi,
Can you try this..

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

View solution in original post

4 Replies
sivakumar1994
Contributor III
Contributor III

please provide details about the dimension used

leenlart
Creator
Creator
Author

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

Hi,
Can you try this..

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

View solution in original post

leenlart
Creator
Creator
Author

Yes!  This worked great!   Thanks for your help!