Change text colour based on variable within an expression

[hogo1701]

Hi

I am trying to achieve an outcome whereby the text colour within a text box changes based on the data value of the current period compared to the previous period. The below continues to result in red text irrespective.

I have tested the maxdate and maxdatepreviousperiod variables and they are working as expected

if (sum({<[Category 2]={'Excess'},[Pay Period]={"$(= '$(vmaxdate)')"}>}Count)>
        sum({<[Category 2]={'Excess'},[Pay Period]={"$(= '$(vmaxdatepreviousperiod)')"}>}Count), rgb(255,0,0), rgb(0,128,0))

[sunny_talwar]

Have you checked if these work to give the right number you expect in a text box?

sum({<[Category 2]={'Excess'},[Pay Period]={"$(= '$(vmaxdate)')"}>}Count)

sum({<[Category 2]={'Excess'},[Pay Period]={"$(= '$(vmaxdatepreviousperiod)')"}>}Count)

[shiveshsingh]

What's the result of your if condition?

[olivierrobin]

you should verify the format of all dates

[hogo1701]

Thanks Sunny. The first returns the correct number but the second returns 0.

[sunny_talwar]

How do you define vmaxdate and vmaxdatepreviousperiod? Also, are you making selection in any date and time related field other than Pay Period?

[hogo1701]

vmaxdate  =date(Max([Pay Period]),'D/MM/YYYY')

vmaxpreviousperiod =date((vmaxdate-14),'D/MM/YYY')

There are no other selections referring to date / time etc.

[sunny_talwar]

How about if you do this

vmaxpreviousperiod

=Date(Max([Pay Period])-14,'D/MM/YYY')

[hogo1701]

Unfortunately still the same outcome. The variable works when returning the date but when I include it in an expression to provide the number it returns 0.

[sunny_talwar]

I don't know if this is a typo, I see a Y missing from the format part... try this

=Date(Max([Pay Period])-14,'D/MM/YYYY')