topic Re: Change text colour based on variable within an expression in QlikView

Change text colour based on variable within an expression

hogo1701 — Wed, 25 Nov 2020 16:16:04 GMT

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))

Re: Change text colour based on variable within an expression

sunny_talwar — Fri, 23 Mar 2018 16:05:13 GMT

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)

Re: Change text colour based on variable within an expression

shiveshsingh — Fri, 23 Mar 2018 16:28:04 GMT

What's the result of your if condition?

Re: Change text colour based on variable within an expression

olivierrobin — Fri, 23 Mar 2018 16:55:25 GMT

you should verify the format of all dates

Re: Change text colour based on variable within an expression

hogo1701 — Sun, 25 Mar 2018 21:27:21 GMT

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

Re: Change text colour based on variable within an expression

sunny_talwar — Sun, 25 Mar 2018 23:50:54 GMT

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

Re: Change text colour based on variable within an expression

hogo1701 — Mon, 26 Mar 2018 00:13:45 GMT

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.

Re: Change text colour based on variable within an expression

sunny_talwar — Mon, 26 Mar 2018 00:22:16 GMT

How about if you do this

vmaxpreviousperiod

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

Re: Change text colour based on variable within an expression

hogo1701 — Mon, 26 Mar 2018 02:50:20 GMT

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.

Re: Change text colour based on variable within an expression

sunny_talwar — Mon, 26 Mar 2018 09:01:50 GMT

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')