Less than or eqal-operator <=

[ChristofZ]

In a formula in a table, I filter a date from my data, using only the Less than or eqal-operator "<=". For date = '21.10.2024', the comparison yields

• (date <= '21.10.2024') = -1 (TRUE)
• ('21.10.2024' <= date) = 0 (FALSE) ????
  

If I compare 2 identical strings with the same date:

• ('21.10.2024' <= '21.10.2024') = -1 (TRUE)

Am I using the wrong operator "<="? Is that a bug in Qlik?

Thanks & regards, Christof

[marcus_sommer]

It's a complex matter and it will always be depending on the specific context if it worked like expected or not and if not why not respectively what happens under the hood.

IMO it makes not much sense to dive deeper else to change the general approach and don't applying any matching or calculation on formatted values else only against pure numbers. It doesn't mean to have formatted fields or strings within the dimensions or as selections but they aren't used for all the calculations else only the appropriate associated numbers.

[ChristofZ]

new experience with < > = :

if I try to display a constant value at date 12/31/2023 by the formula:

Sum(
ALL 
{1<
myDate = {"$(= YearEnd(today - 1, -1) )"}
>}
someNumbers
)

the filter condition is correctly interpreted as

myDate = {"12/31/2023"}

but the result is zero, even though there are data. The syntax

{"$(= '=' & YearEnd(today - 1, -1))"}

doesn't help either. If I include the next day in the filter like so:

Sum(
ALL 
{1<
myDate = {"$(= '>=' & YearEnd(today - 1, -1) & '<' & YearStart(today - 1) )"}
>}
someNumbers
)

the filter condition is correctly interpreted as

myDate = {">=12/31/2023<01/01/2024"}

and the result is correct.

??

[marcus_sommer]

Like above already hinted such approaches should be avoided and only pure numbers be used to perform all matching and calculation. No struggling with all the formatting-stuff and the interpretation-context is (nearly) on all places identically.

By applying: { < Field = {Value]>} the string-representation of the values in Field and of the specified Value will be compared - but {"Value"} isn't a string else {'Value'} would be one.  By using {">=Value"} a numerical comparison happens. Further yearend() returned no date else a timestamp.

There are a lot of things which needs to be considered by comparing values within scripts, macros, variables, set analysis, ... everywhere is the context more or less different to each other. Of course it's technically possible to handle everything correct - but even for experienced developers it's quite hard to implement it. And it is completely unnecessary to waste the efforts with such stuff just by using pure numbers and creating all needed information within the data-model respectively the master-calendar.

[jochem_zw]

did you floor your myDate? what is the result of num(myDate)? Decimals? 

[ChristofZ · Accepted solution]

thank you jochem_zw:

myDate = "04.11.024" (why "024" btw??)
Floor(myDate) = 45600
num(myDate) = 45600

meanwhile I filter everything "< the-next-date", like last year:

myDate = {"$(= '>=' & YearStart(today -1, -1) & '<' & YearStart(today -1) )"}

seems to work ...

[jochem_zw]

ok, so if you want to filter on exact date you have to use your date format: myDate ={"04.11.024"} 

[ChristofZ · Accepted solution]

new development: I corrected our "inofficial" 3-digit year format to a 4-digit one, and suddenly the filtering a là

myDate = {"31.12.2023"}

worked - applogies to the flawlessly working Qlik Sense and thanks for the help, guys! 😇