Count if based on date

[lfholland]

I am trying to count the number of orders where the promised date and invoiced date are the same.  I have tried the below formula along with several variations.  The answer keeps coming up as zero.  Perhaps one issue is the date formats of each dimension?  Invoice Date is MM/DD/YY and Promise Date is that plus the time...

Count(DISTINCT {<InvoiceDate={"=PromiseDate"}>} OrderNumber)

[petter · Accepted solution]

Make sure that you have a field in the table that is a unique row identifier. If you don't have it already you can create it by adding this in the load statement in your load script while loading the table:

LOAD

      ...

      RowNo() AS Unique_RowID,

      PromiseDate,

      InvoiceDate,

....

Then you can use this expression:

Count(DISTINCT {<Unique_RowID={"=InvoiceDate=PromiseDate"}>} OrderNumber)

[howiekrauth]

Create another field, something like "PromiseDateFormatted", which looks like this:

date(PromiseDate, 'MM/DD/YYYY') AS PromiseDateFormatted

Then replace "PromiseDate" with "PromiseDateFormatted" in your set analysis.

[lfholland]

Thank you Petter and Howie!  I had to do both but with the two combined, it worked!

[lfholland]

I have a follow up question.  I have one column that counts the orders that are on time and one that counts the orders that are complete.  Now I need a column that counts the orders that are both on time and complete.  The formula appears to only count the orders that are on time.  Thank you for your help!

Count(DISTINCT {<%Order={"=QtyOrd=QtyShip"},%Order={"=InvoiceDate=PromiseDate"}>} OrderNumber) 

[petter]

In a single set expression you can only refer to a single field once. You have referred to %Order twice and Qlik will simply ignore the first (I think) and honour the last one...

So you will have to rather use AND in a single search and make an expression like this:

Count(DISTINCT {<%Order={"=QtyOrd=QtyShip AND InvoiceDate=PromiseDate"}>} OrderNumber)

[lfholland]

Thank you!!!