Date -> Year query

[knightwriter]

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.

[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]

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]

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]

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 · Accepted solution]

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.

[sunny_talwar]

Super