Difference in two similar date expressions

[rasmusnielsen]

Hi,

Can someone explain me why there is a difference in the two following set expressions:

1: Avg({$<Date={"=$(vDate1)"}>}[Discount])
2: Avg({$<Date={"$(vDate1)"}>}[Discount])

I need the average discount for the date in the vDate1 variable. When I verify the data, #2 gives me the correct result. It looks like #1 includes more data than I expect. 

Thanks!

[OmarBenSalem · Accepted solution]

the first expression is false, so it's not taking ur condition into consideration but doing the avg discount of all what qlik sees (depends on what u're selecting)

the second expression is correct:

in fact, it's a syntax thing:

1) if ur condition is numeric;

Year={2019} no need of using the '=' sign nore the quotes

2) if ur condition is a string

Country={'Tunisia'} : u have to use the quotes 

3) if it's an expression : 

max(Year)

since it's a calculation : the '=' sign is needed

and when u use the '= 'sign, u have to surround ur expression with the $() to force the calculation.

the $() sign in this case is like the "let" in the script:

let 5+3 => 8

set 5+3 =>'5+3'

then surround the whole with double quotes (when it's a calculation)

FYI:

value={'>99'} : a value named >99

value={">99"} : any value > 99 (because of the double quotes)

FYI 2 : when using the > , <, >= or <= signs, no need to use the $() to force the calculation since it's already has been "forced" with those signs..

so, it would be :

Year={"$(=max(Year))"}

now, when u create a variable, it already includes the '=', since it's calculated, so u just surround it with the $() sign

Year={"$(vMaxYear)"}

Hope that was clear

Omar BEN SALEM

[pradosh_thakur]

---

@OmarBenSalem wrote:

the first expression is false, so it's not taking ur condition into consideration but doing the avg discount of all what qlik sees (depends on what u're selecting)

---

That was some explanation Omar. 🙂

As per my understanding when we use double quotes and initiate with a "=' it means we do a binary search and anything other than zero is true. I guess you said the same thing in the quoted part.

SO 

{<Year={"=2019"}>} will select all the year selected/available for selection as 2019 > 0

where as 

{<Year={2019}>}  will filter out the 2019 data.

[rasmusnielsen]

Thanks! Makes perfect sense 🙂