Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
QlikSenseUser2
Contributor III
Contributor III

Background Color expression by date

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

 

QlikSenseUser2_0-1626311548631.png

 

17 Replies
QlikSenseUser2
Contributor III
Contributor III
Author

It returned 

QlikSenseUser2_0-1626362841381.png

 

Kushal_Chawda

@QlikSenseUser2  your dates contains future dates till 2051? In that case what is your reference date to colour the dates?

QlikSenseUser2
Contributor III
Contributor III
Author

But when  I try 

Aggr(max(LAST_INSPECTION_DATE),TRAILER) it doesn't

Do you think this is the issue? That is the expression for that measure (Last Inspection Date)

QlikSenseUser2_1-1626362961110.png

 

QlikSenseUser2
Contributor III
Contributor III
Author

No only until 2021.

Kushal_Chawda

@QlikSenseUser2  see your result in previous reply. It is showing 2051 year. Looks like your data is not correct.  Also, you cannot use just aggr in KPI object. You also need some aggregation on top of that something like.. sum(aggr(max(Sales),Product))

QlikSenseUser2
Contributor III
Contributor III
Author

Sorry, The max date is 5/20/2051, but I'm just trying to show 1 year from the current LAST_INSPECTION_DATE.

Kushal_Chawda

@QlikSenseUser2  try below. Make sure that you are using below same expression on correct column. If this still did not work share the sample qvf file

=if(LAST_INSPECTION_DATE > addyears(Today(),-1) and LAST_INSPECTION_DATE <= Today(), Green(),Red())

 

QlikSenseUser2
Contributor III
Contributor III
Author

Thanks that worked!!!