topic Re: Date variable in background color expression in App Development

Date variable in background color expression

palo173 — Sat, 16 Nov 2024 00:53:09 GMT

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

Re: Date variable in background color expression

agigliotti — Wed, 30 Dec 2020 15:29:59 GMT

Hi @palo173 ,

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

Re: Date variable in background color expression

palo173 — Wed, 30 Dec 2020 16:01:40 GMT

=vStartMonth in text object return 01.12.2020

Re: Date variable in background color expression

agigliotti — Thu, 31 Dec 2020 08:31:50 GMT

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.

Re: Date variable in background color expression

palo173 — Thu, 31 Dec 2020 09:38:36 GMT

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

Re: Date variable in background color expression

tresB — Thu, 31 Dec 2020 09:54:27 GMT

Try like:

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

Re: Date variable in background color expression

palo173 — Thu, 31 Dec 2020 10:13:42 GMT

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

Re: Date variable in background color expression

tresB — Thu, 31 Dec 2020 10:20:12 GMT

Could you share your sample app to check?

Re: Date variable in background color expression

palo173 — Thu, 31 Dec 2020 10:52:26 GMT

It is not possible to share data from qlik sense.

Re: Date variable in background color expression

rubenmarin — Thu, 31 Dec 2020 11:07:57 GMT

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

Re: Date variable in background color expression

agigliotti — Thu, 31 Dec 2020 11:16:52 GMT

let's try this:
=If( Count( {< work_day = {'1'}, Date_1 = {"$(=Date(Floor(Monthstart(Today()-1))+5, 'DD.MM.YYYY'))"} >} distinct total Date_1 ) > 0, rgb(162,233,171) )