topic Re: Count if based on date in QlikView

Count if based on date

lfholland — Tue, 07 Aug 2018 20:50:03 GMT

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)

Re: Count if based on date

petter — Tue, 07 Aug 2018 22:14:37 GMT

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)

Re: Count if based on date

howiekrauth — Tue, 07 Aug 2018 22:19:37 GMT

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.

Re: Count if based on date

lfholland — Wed, 08 Aug 2018 13:12:32 GMT

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

Re: Count if based on date

lfholland — Wed, 08 Aug 2018 13:22:44 GMT

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)

Re: Count if based on date

petter — Wed, 08 Aug 2018 13:45:49 GMT

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)

Re: Count if based on date

lfholland — Wed, 08 Aug 2018 13:53:25 GMT

Thank you!!!