topic Count Distinct or aggr in QlikView

Count Distinct or aggr

Mon, 17 May 2010 11:06:19 GMT

Hi all,

I have an easy question (but after many test actually i'm confused...)

I would like to know on each dimension (Market Area)

- how many order i have count(distinct OrderId) It works

-----------------------------------------------------------------------------------------------------------------------------------------------------

how many order have a price >100€ Wrong Result

=sum(aggr(if(Price >100, 1,0 ) , OrderId) ) or

if(Price>100, count (distinct OrderId))

_____________________________________________________________

how many lines have a price > 100€.

sum(if(O$PREZ>10), 1,0)

Wrong !

Many Many Thanks

Claudio

spsrk_84 — Mon, 17 May 2010 11:14:16 GMT

Hi,

Try in this way

Count( if(Price>100, distinct OrderId))

Mon, 17 May 2010 11:19:03 GMT

hi,

probably u can use this

Count(distinct If(aggr(Sum(Price),OrderId)>100,OrderId))

i haven't tested it but u can give it a try

thanks

Mon, 17 May 2010 11:29:31 GMT

try this

count(distinct(if(Price>100,orderid)))

regards

Peter

Mon, 17 May 2010 19:45:00 GMT

Many thank

they work perfectly (The first one was wrong)

Claudio