Date variable in background color expression

[palo173]

I try to use variable vStartMonth in  background color expression
vStartMonth = Date(Floor(Monthstart(Today()-1)), 'DD.MM.YYYY')

When I use specific date it works:
If(Count({<work_day={'1'}, Date_1={'1.12.2020'} >}distinct total Date_1)>0, rgb(162,233,171))

When I use variable it does not work:
If(Count({<work_day={'1'}, Date_1={'$(vStartMonth)'} >}distinct total Date_1)>0, rgb(162,233,171))
I tried plenty of tests with variable but everything was wrong.

Could you help me?
Thank you

[agigliotti]

Hi @palo173 ,

What do you get if you put vStartMonth variable in a text object ? 1.12.2020 or 01.12.2020 ?

[palo173]

=vStartMonth in text object return 01.12.2020

[agigliotti]

then the below expression:

=If( Count( {< work_day = {'1'}, Date_1 ={"$(vStartMonth)"} >} distinct total Date_1 ) > 0, rgb(162,233,171) )

should work for you.

be sure the field Date_1 have the same format DD.MM.YYYY as your variable vStartMonth.

[palo173]

Thank you for your answer. The first option,, which I wrote in set analysis was your way.

But you had very good point, that Date_1 have to be in the same format. And it was not. I corrected format in field Date_1 and it works. 

Can I ask second question?
I want to add 5days in variable. What is wrong in expression?

=If( Count( {< work_day = {'1'}, Date_1 ={"=Date($(vStartMonth)+5,'DD.MM.YYYY'"} >} distinct total Date_1 ) > 0, rgb(162,233,171) )

[tresesco]

Try like:

=If( Count( {< work_day = {'1'}, Date_1 ={"$(=Date($(vStartMonth)+5,'DD.MM.YYYY'))"} >} distinct total Date_1 ) > 0, rgb(162,233,171) )

[palo173]

Sorry Tresesco, but it is not correct. Count returns 0, but it is not true.

[tresesco]

Could you share your sample app to check?

[palo173]

It is not possible to share data from qlik sense.

[rubenmarin]

Hi  @palo173, maybe with =If( Count( {< work_day = {'1'}, Date_1 ={"$(=Date(Date#('$(vStartMonth)','DD.MM.YYYY')+5,'DD.MM.YYYY'))"} >} distinct total Date_1 ) > 0, rgb(162,233,171) )