count distinct with a condition

[#1]

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 ?

[yvesqlik]

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!

[#3]

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

[hic]

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

HIC

[#5]

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

Try this:

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

[#6]

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

Try this:

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