Set expression date less than today

[imac]

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

[sunny_talwar · Accepted solution]

Try this

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

[imac]

Beautiful!! Thanks Sunny

[sunny_talwar]

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