Trying to do conditional formatting on value expressions

[SimonAdler]

Hi folks, 

I'm trying to change the background color of a cell, based on a calculated value. 
The use case is looking to see which invoices are due to be paid or overdue.

So if the days to pay are less than or equal to 0 that should be red, between 0-10 amber (ie they are coming up to be paid).

I'm not even sure if this is possible as I can't find any reference to conditional formatting using expressions.

Thanks

aggregator.displayformat=HTML
aggregator.function=AVG
aggregator.valueexpression=DATEDIFF({DE:Invoice Date},$$TODAY)
displayname=Days to Invoice Payment
linkedname=direct
styledef.case.0.comparison.icon=false
styledef.case.0.comparison.leftmethod=DATEDIFF({DE:Invoice Date},$$TODAY)
styledef.case.0.comparison.lefttext=DATEDIFF({DE:Invoice Date},$$TODAY)
styledef.case.0.comparison.operator=lt
styledef.case.0.comparison.operatortype=double
styledef.case.0.comparison.righttext=0
styledef.case.0.comparison.trueproperty.0.name=bgcolor
styledef.case.0.comparison.trueproperty.0.value=eac6c9
styledef.case.0.comparison.truetext=
styledef.case.1.comparison.icon=false
styledef.case.1.comparison.leftmethod=DATEDIFF({DE:Invoice Date},$$TODAY)
styledef.case.1.comparison.lefttext=DATEDIFF({DE:Invoice Date},$$TODAY)
styledef.case.1.comparison.operator=between
styledef.case.1.comparison.operatortype=double
styledef.case.1.comparison.righttext=0
styledef.case.1.comparison.righttextrange=10
styledef.case.1.comparison.trueproperty.0.name=bgcolor
styledef.case.1.comparison.trueproperty.0.value=feecc8
styledef.case.1.comparison.truetext=
textmode=true
valueexpression=DATEDIFF({DE:Invoice Date},$$TODAY)
valueformat=HTML

[lgaertner]

Hello Simon,

As written here, text mode does not seem to be able to do conditional stylings based on values which are calculated on the fly.

What you can do is to create a custom calculated field using your expression and compare to that.

Regards

Lars

[Sven-iX]

You can compare two fields instead of using DATEDIFF

https://experienceleague.adobe.com/en/docs/workfront/using/reporting/reports/text-mode/compare-field...

So in your case: 

styledef.case.0.comparison.icon=false
styledef.case.0.comparison.leftmethod=DE:Invoice Date
styledef.case.0.comparison.lefttext=DE:Invoice Date
styledef.case.0.comparison.operator=lt
styledef.case.0.comparison.operatortype=date
styledef.case.0.comparison.righttext=$$TODAY
styledef.case.0.comparison.trueproperty.0.name=bgcolor
styledef.case.0.comparison.trueproperty.0.value=eac6c9
styledef.case.0.comparison.truetext=

However - to get the "between 0 and 10 days" you will need to create a calculated custom field that you add to the report e.g. 

IF(DATEDIFF({DE:Invoice Date},$$TODAY)=0, "Due today",IF(DATEDIFF({DE:Invoice Date},$$TODAY)<0, "Late",IF(DATEDIFF({DE:Invoice Date},$$TODAY)<10, "Due soon","")))

Then you can define 3 rules based on the value of that calculated field.