5 Replies Latest reply: May 31, 2012 9:28 AM by DK_ASN RSS

count distinct with a condition

freaksmj

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 [table] 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
    Yves Ndjoli

    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
    Jorge Villalobos

    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
    Henric Cronström

    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
    DK_ASN

    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
    DK_ASN

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

    Try this:

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