topic Set expression date less than today in QlikView

Set expression date less than today

imac — Wed, 11 Jul 2018 10:38:34 GMT

Learning Qlikview by myself and I have read through a LOT of threads about this very same question and must have tried 50 different things but for the life of me, it just does not work.

I am trying to use this expression in a text object, but it always returns 0

='Of Which Overdue: ' & num(sum({$<DueDate={"<$(=(Today()))"}>}InvoiceBalance), '$#,###.00')

DueDate is exactly this:

"09/07/2018 00:00:00" (its DD/MM/YYYY HH:MM:SS)

InvoiceBalance is exactly this:

"49080"

I am expecting the text box to say (since DueDate is less than today):

"Of Which Overdue: $49,080"

Instead I see:

"Of Which Overdue: $0.00"

Any help appreciated, I am pulling my hair out

Re: Set expression date less than today

sunny_talwar — Wed, 11 Jul 2018 11:06:51 GMT

Try this

='Of Which Overdue: ' & Num(Sum({$< DueDate = {"<$(=TimeStamp(Today(), 'DD/MM/YYYY hh:mm:ss'))"}>} InvoiceBalance), '$#,###.00')

Re: Set expression date less than today

imac — Wed, 11 Jul 2018 11:22:35 GMT

Beautiful!! Thanks Sunny

Re: Set expression date less than today

sunny_talwar — Wed, 11 Jul 2018 11:24:36 GMT

Set analysis comparison is format sensitive... you were comparing DD/MM/YYYY hh:mm:ss to probably DD/MM/YYYY... which doesn't work in QlikView or Qlik Sense.

Read more about Dates in set analysis here

Dates in Set Analysis