How use variables and functions in a set Analysis chart function E() or P()

[rdugg]

I'm stuck with some some set analysis in a chart function and cannot clearly find an answer if it is a limitation of Qlik or a misunderstanding from my side.

I have the expression of a KPI that goes like :

Count({<id = P({<start_date={">31/12/2023"}>})> *
   <id = E({<end_date={"<=03/01/2025"}>})>} distinct id)

which works well.

(I use P() and E() in it because this looks like the best way to filter the way I want some null values on dates)

now I want to generalize that expression and I'm trying to replace

31/12/2023 with $(vMyStartDateVariable) and

03/01/2025 with Today()

but any of those 2 changes doesn't work.

The validator at the bottom of the expression edition window says it is ok, but i wonder if the validator has a clue of what it's talking about because $(vMyStartDateVariable) and Today() are in a double quoted string and it seems like the parser only think it is text (syntax color stays green for the whole double quoted string).

Any idea ?

[marcus_sommer · Accepted solution]

Variables will be working for such scenarios but the way how they are created and which kind of data the values have will depend the syntax of the calls in regard to the used context. And yes, sometimes it could become tricky ...

For testing reasons I suggest to split the task into two parts - maybe like:

Count({<id = P({<start_date={">vMyStartDateVariable"}>})>} distinct id)

and

Count(<id = E({<end_date={"<=$(=today())"}>})>} distinct id)

to simplify the troubleshooting which just provide some alternate ways to set the calls but the most complexity will remain because the variables contains string-values which needs to be interpreted as numbers in the wanted way.

Much more simple is to use pure numbers, maybe created like:

vStart: num(makedate(2023,12,31))
vEnd: num(today())

and then it may look like:

Count({<id = P({<start_date={">$(vStart)"}>})> *
   <id = E({<end_date={"<=$(vEnd)>})>} distinct id)

[rdugg]

I think I have found a solution

with the help of these explanations

https://community.qlik.com/t5/Design/The-Magic-of-Variables/ba-p/1465499

and the key to late computation of a variable value with the preceding equal :

https://community.qlik.com/qlik-blogpost/3996

[rdugg]

So this part wasn't working

31/12/2023 with $(vMyStartDateVariable)

because my variable wasn't in the right format. I added a Date(xxx, "DD/MM/YYY") to it

For this one,

03/01/2025 with Today()

Your example made me learn about the ${=function_call()}

which I wasn't aware existed.