Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

count over a sum within IF

Hi All,

I want to calculate an expression for counting the distinct product ID where sum of quantity is 3.....

I am not able to write an expression for the same. Please help me with it.

Thanks

Arun

1 Solution

Accepted Solutions
anbu1984
Master III
Master III

=Count(DISTINCT If(Aggr(Sum(Quantity),VENDOR_ID)=3,  VENDOR_ID))

View solution in original post

17 Replies
Michiel_QV_Fan
Specialist
Specialist

Add an addional table with a subtotal to your (= group by) script. Use the result of that subtotal in your set analysis.

excel:

LOAD A,

     B

FROM

(ooxml, no labels, table is Sheet1);

subtotal:

load

  A,

  sum(B) as tot_A

Resident excel

group by A;

Expression:

sum({<tot_A ={ '3'}>}B)

Not applicable
Author

Is there any other way in which I can do this, I mean without adding something to the script. May be through some function?

Thanks

Arun

Anonymous
Not applicable
Author

hi,

=If((sum(Quantity)='3'),count(Product))

regards

Neetha

Not applicable
Author

Already tried this. Not working.

anbu1984
Master III
Master III

=Count(DISTINCT{<ProdID={'=Aggr(Sum(Qty),ProdID)=3'}>} ProdID)

Load * Inline [

ProdID,Qty

100,2

101,4

102,3

103,5

100,1

101,5

105,1

106,1];

Anonymous
Not applicable
Author

Hello,

Try count(if(sum(Quantity)=3,Product) ). Depends on your dimension too.

More elegant solution is neetha's.

BR

Serhan

Anonymous
Not applicable
Author

Hi Arun,

It should work .

Please check syntax.

Regards

Neetha

ashwanin
Specialist
Specialist

Hi,

Solution provided above should work for the asked scenario .

However if its not working, then can you please upload a sample .

Not applicable
Author

Hi,

Please find attached the data file. I want to do a count(Distinct(VENDOR_ID)) where sum(Quantity)>3,4 etc.

I should be able to filter the result on the basis of TIME_ID & PROD_ID.