count over a sum within IF

[#1]

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

[Michiel_QV_Fan]

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)

[#3]

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]

hi,

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

regards

Neetha

[#5]

Already tried this. Not working.

[anbu1984]

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

Hello,

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

More elegant solution is neetha's.

BR

Serhan

[Anonymous]

Hi Arun,

It should work .

Please check syntax.

Regards

Neetha

[ashwanin]

Hi,

Solution provided above should work for the asked scenario .

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

[#10]

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.