topic Re: New Set Analysis behavior on numeric (money) field in QlikView

New Set Analysis behavior on numeric (money) field

kswindlehurst — Tue, 28 Jun 2022 16:18:54 GMT

After a recent upgrade to May 2021 SR2, one chart expression's set analysis modifier started failing. It's an alert list of accounts that are either missing a [Patient Responsibility] entry or it's set to zero. [Patient Responsibility] is a numeric field in the internal table and Qlik is automatically interpreting it as a Money field (default format in list object includes "$").

Prior to the upgrade this worked:

Count({$-<[Patient Responsibility]={">0"}>} [Account Number])

Afterwards, it only worked by adding the "$"

Count({$-<[Patient Responsibility]={">$0"}>} [Account Number])

Qlik must be storing the data as a dual with the "$" included in the string component, right? Aren't operations attempted on the numeric component first? And if the double-quoted modifier now causes priority to change to the string, how is it even working? A mathematical comparison (>) against a string? Is this known and expected?

Re: New Set Analysis behavior on numeric (money) field

marcus_sommer — Wed, 29 Jun 2022 11:06:16 GMT

Was it previously also interpreted as money and is it really a dual-value?

- Marcus

Re: New Set Analysis behavior on numeric (money) field

kswindlehurst — Thu, 30 Jun 2022 22:11:00 GMT

Alas, I don't have an easy way to stand up the old version. The MSSQL datatype in the source is money - does Qlik get metadata over OLEDB? But even if the interpretation changed, the new set analysis behavior still doesn't make sense to me, and that's the most concerning. If it's not populating a string with a money format, why does it only work with a "$" in my comparison? That's what makes me think there has to be a string element populated. If it's just a display preference, shouldn't set analysis ignore that? But there could be a totally illogical explanation that I can't predict. The field in a list object always follows the Number alignment setting, not the Text setting. There's no tag on the field other than $numeric.

Re: New Set Analysis behavior on numeric (money) field

marcus_sommer — Fri, 01 Jul 2022 07:21:36 GMT

Qlik hasn't a data-type concept else the loaded data were interpreted - as numeric, strings, mixed and if possible as dual-values - depending on the interpretation-variables, any direct converting/formatting and also the order in which the data were loaded.

In general is your understanding of the set analysis interpretation right that a > or >= or < or <= forced a numeric comparison. But now it could become a bit tricky if the values are strings they will also be numeric evaluated. I could remember (rarely) postings in which it were discussed but unfortunately I forgot the details.

Before calling your issue a (new) bug and/or investigating it in this way I would suggest to make sure that there is really everything right within the data. You mentioned that you couldn't compare it directly against the older release but you could add some information to be able to evaluate the data in multiple ways, like adding:

Field,
num(Field),
money(Field),
floor(Field),
frac(Field),
text(Field),
isnum(Field),
istext(Field),
isnull(Field),
len(Field),
recno(),
rowno(),
...

Are really all data like expected?

Beside this I noticed that you use $- as identifier for the set analysis - it might be helpful to remove it and/or to transfer the logic to the modifier to get further insights if what might happens.

- Marcus