topic Re: count over a sum within IF in QlikView

count over a sum within IF

Thu, 11 Dec 2014 12:20:52 GMT

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

Re: count over a sum within IF

Michiel_QV_Fan — Thu, 11 Dec 2014 12:28:03 GMT

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,

FROM

(ooxml, no labels, table is Sheet1);

subtotal:

load

sum(B) as tot_A

Resident excel

group by A;

Expression:

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

Re: count over a sum within IF

Thu, 11 Dec 2014 12:30:43 GMT

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

Re: count over a sum within IF

Anonymous — Thu, 11 Dec 2014 12:33:51 GMT

hi,

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

regards

Neetha

Re: count over a sum within IF

Thu, 11 Dec 2014 12:36:23 GMT

Already tried this. Not working.

Re: count over a sum within IF

anbu1984 — Thu, 11 Dec 2014 12:39:56 GMT

=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];

Re: count over a sum within IF

Anonymous — Thu, 11 Dec 2014 12:51:09 GMT

Hello,

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

Re: count over a sum within IF

Anonymous — Thu, 11 Dec 2014 12:53:26 GMT

Hi Arun,

It should work .

Please check syntax.

Regards

Neetha

Re: count over a sum within IF

ashwanin — Thu, 11 Dec 2014 12:58:32 GMT

Hi,

Solution provided above should work for the asked scenario .

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

Re: count over a sum within IF

Thu, 11 Dec 2014 13:06:20 GMT

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.

Re: count over a sum within IF

Anonymous — Thu, 11 Dec 2014 13:18:06 GMT

Hi,

Try:

=If((sum(Quantity)='3'),count(Distinct VENDOR_ID)) - For individual count

=If((sum(Quantity)='3'),count(Total Distinct VENDOR_ID)) - For total count of all products

Regards

Neetha

Re: count over a sum within IF

Thu, 11 Dec 2014 13:25:03 GMT

TRIED. But not working.

I took time ID as the dimension and expression stated by you in the above comment but not working.

Re: count over a sum within IF

Anonymous — Thu, 11 Dec 2014 13:28:12 GMT

Can you post the snapshot of the expression please.

Regards

Neetha

Re: count over a sum within IF

Thu, 11 Dec 2014 13:29:03 GMT

Hi Arun,

Load * Inline [

Cat,ProdID,Qty

A,100,2

A,101,4

A,102,3

A,103,5

A,100,3

B,101,5

B,105,3

B,106,3];

Dim : Cat

Expression : =count(if(aggr(sum(Qty),ProdID)=3,ProdID))

Thanks,

Prakash

Re: count over a sum within IF

its_anandrjs — Thu, 11 Dec 2014 13:29:27 GMT

Try this expression

=count(if( Quantity >= 3, VENDOR_ID))

Regards

Anand

Re: count over a sum within IF

anbu1984 — Thu, 11 Dec 2014 13:30:47 GMT

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

Re: count over a sum within IF

Anonymous — Thu, 11 Dec 2014 13:32:09 GMT

See attached

Re: count over a sum within IF

Thu, 11 Dec 2014 13:56:04 GMT

Thanks a lot. This expression is working fine.