topic count distinct with a condition in QlikView

count distinct with a condition

Wed, 30 May 2012 19:32:48 GMT

hi
i'm new to qlikview
i want to make an expression in Qlikview which will count number of late order

in SQL it will be like this
SELECT [OrderID] FROM

where ShippedDate>RequiredDate

i try this expression

Count(DISTINCT if (ShippedDate>RequiredDate,OrderID)
but it give me wrong result

can anyone help me ?

count distinct with a condition

yvesqlik — Wed, 30 May 2012 20:26:37 GMT

hi,

i suggest first to load the order from the source:

Temp:

SELECT [OrderID] FROM [table] where ShippedDate>RequiredDate;

Table:

load distinct [OrderID] resident Temp;

left join (Table)

load *,

1 as count

resident Table;

Count:

sum(count)

resident Table;

let me know if my solution can help u.

Best regards!

count distinct with a condition

Wed, 30 May 2012 20:48:25 GMT

For doing this in an expression you need to use ser analysis.

Do it like this:

first store the value of the "RequiredDate" in a variable, do it in the script or in the variable panel.

Let varReqDate = RequireDate ; //this assuming the format is clean

then...

Count(Distinct {$<ShippedDate={">$(varReqDate)"}>}OrderID)

this might solve your problem

count distinct with a condition

hic — Thu, 31 May 2012 12:39:00 GMT

Your expression "Count(DISTINCT if (ShippedDate>RequiredDate,OrderID))" should work fine. Are you sure the dates are interpreted correctly? (So they are numeric)

HIC

count distinct with a condition

Thu, 31 May 2012 13:27:39 GMT

You are missing a "(" between distinct and if ( which then also needs an additional ")" after OrderID)

Try this:

count(distinct(if(ShippedDate>RequiredDate,OrderID)))

Re: count distinct with a condition

Thu, 31 May 2012 13:28:16 GMT

You are missing a "(" between distinct and if ( which then also needs an additional ")" after OrderID)

Try this:

count(distinct(if(ShippedDate>RequiredDate,OrderID)))