Background Color expression by date

[QlikSenseUser2]

I'm trying to set a Background Color expression by date. I want it to show green if within a year of the Aggr(max(LAST_INSPECTION_DATE),TRAILER)  or Red if greater than a year of the Aggr(max(LAST_INSPECTION_DATE),TRAILER).

I have 

IF(Year(Aggr(max(LAST_INSPECTION_DATE),TRAILER)) > AddYears(Aggr(max(LAST_INSPECTION_DATE),TRAILER),1), LightRed(),Green())

And I get this all green

[avinashelite]

are you looking for the moving 1 year date or the current year to date ?

[QlikSenseUser2]

Moving 1-year date. I would like for it to go by the  Last Inspection Date.

[Anil_Babu_Samineni]

You can try something this?

IF(Year(LAST_INSPECTION_DATE) = (Year(LAST_INSPECTION_DATE)+1), LightRed(),Green())

[QlikSenseUser2]

It's still showing all green 

[Kushal_Chawda]

@QlikSenseUser2  so let's say last_inspection_date is "17-Jul-2021", so which dates should be green and which one should red? You can specify ranges to be clear

[QlikSenseUser2]

17-Jul-2020 and before should be Red everything else Green

[Kushal_Chawda]

@QlikSenseUser2  then below should work

= if( LAST_INSPECTION_DATE > addyears(max(total LAST_INSPECTION_DATE),-1), Green(),red())

[QlikSenseUser2]

It's now showing all Red

[Kushal_Chawda]

@QlikSenseUser2  Not sure but it's working fine for me. see the below. "15-Jul-2021" is latest date. Make sure that your LAST_INSPECTION_DATE is in proper Date format. To check that use max(LAST_INSPECTION_DATE) in KPI object, if it returns value then it's fine else there is format issue. In that case you need to first convert that field to proper date format