Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
knightwriter
Creator III
Creator III

Date -> Year query

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.

1 Solution

Accepted Solutions
knightwriter
Creator III
Creator III
Author

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.

View solution in original post

8 Replies
sunny_talwar

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

knightwriter
Creator III
Creator III
Author

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.

sunny_talwar

So are you trying something like this?

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

knightwriter
Creator III
Creator III
Author

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?

sunny_talwar

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?

knightwriter
Creator III
Creator III
Author

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.

knightwriter
Creator III
Creator III
Author

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.

View solution in original post

sunny_talwar

Super