topic Re: Date -> Year query in QlikView

Date -> Year query

knightwriter — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

Quick query, I have a date field [Car Sale.Anticipated Close Date] and I want to show everything that is overdue in the past two years ie if the anticipated close date is less than today it is overdue.

I have tried the following:

Year([Car Sale.Anticipated Close Date]) <= (Year(Today()) - 1)

I had also used the below to no avail:

InYear([Car Sale.Anticipated Close Date],today(),0) AND [Car Sale.Anticipated Close Date] < Today()

Hoping someone can help,

Thanks in advance.

Re: Date -> Year query

sunny_talwar — Thu, 25 Aug 2016 09:39:28 GMT

Where are you using this? In the script as a if statement or within the where statement or is this use in front end?

Re: Date -> Year query

knightwriter — Thu, 25 Aug 2016 09:43:34 GMT

Hi Sunny,

I had it originally as an Expression in a straight table but this showed everything. Im trying to use this in the Dimension section on the field name.

Re: Date -> Year query

sunny_talwar — Thu, 25 Aug 2016 09:48:11 GMT

So are you trying something like this?

If(Year([Car Sale.Anticipated Close Date]) <= (Year(Today()) - 1), [Car Sale.Anticipated Close Date])

Re: Date -> Year query

knightwriter — Thu, 25 Aug 2016 09:55:02 GMT

Yes this works sort of, I want the table to show only the 200 or so overdues in the straight table rather than all the historic cases.

I want to do this without changing the script. Is this possible?

Re: Date -> Year query

sunny_talwar — Thu, 25 Aug 2016 09:56:38 GMT

So when you say it sort of work, what is the part that is working? Did you also remember to check 'Suppress When Value Is Null' on the dimension tab for this calculated dimension?

Re: Date -> Year query

knightwriter — Thu, 25 Aug 2016 10:08:41 GMT

What works is that I can see the data from this year and last year (as per the formula) but also shows all the overdue data from 2003.

es, I have selected the suppress button.

Re: Date -> Year query

knightwriter — Thu, 25 Aug 2016 10:41:50 GMT

Hi Sunny,

So I solved it by using:

=If([Car.Status]='Open' AND InYear([Car.Anticipated Close Date],today(),0) AND [Car.Anticipated Close Date] < Today(),[Car Opp Id])

Thanks for your suggestions.

Re: Date -> Year query

sunny_talwar — Thu, 25 Aug 2016 10:44:26 GMT

Super