Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

count distinct with a condition

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 ?

5 Replies
yvesqlik
Partner - Contributor III
Partner - Contributor III

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!

Not applicable
Author

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
Former Employee
Former Employee

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

HIC

Not applicable
Author

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

Try this:

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

Not applicable
Author

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

Try this:

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