topic Re: Help with Set Expression in QlikView

Help with Set Expression

schneider2037 — Wed, 25 Nov 2020 16:16:04 GMT

I am trying to learn Set expressions to correct problems with existing dashboards for my company.

While I understand the basics of set expressions, I'm lost in the ones below, where there's a fieldname followed by an = then followed by a comma then another field and =, et cetera.

I know that the first is for CURRENT year whereas the second is for PRIOR year (based on our internal calendar we reference):

sum({<FISCALMO=,[Calendar Fiscal Month]=,[FISCALYR]={"$(=max({1} [FISCALYR]))"}>}SERVICE_ISSUE_COUNT)

sum({<FISCALMO=,[Calendar Fiscal Month]=,FISCALYR={"$(=max({1} FISCALYR)-1)"}>}SERVICE_ISSUE_COUNT)

I'm used to seeing sum({$<Field1 = {FieldValue1}, Field2 = {FieldValue2}>} SERVICE_ISSUE_COUNT), which isn't what the above follows???

Can anyone "human speak" what the above expressions are attempting? It's returning...answers, just not 100% if they're correct answers.

Thanks in advance!
Jonathan

Re: Help with Set Expression

ogautier62 — Fri, 08 Jun 2018 11:42:48 GMT

Hi,

that seems to react as no selection is taken in account for the field,

so equivalent to 1<field = {'*'}>

regards

Re: Help with Set Expression

avkeep01 — Fri, 08 Jun 2018 11:43:00 GMT

Hi Jonathan,

I've got some useful documentation for you on this topic.

Translation:

sum({<FISCALMO=,[Calendar Fiscal Month]=,[FISCALYR]={"$(=max({1} [FISCALYR]))"}>}SERVICE_ISSUE_COUNT)

SUM(SERVICE_ISSUE_COUNT) Where selections on FISCALMO, [Calendar Fiscal Month] are ignored. = means don't do anything with the selection.

Then for [FISCALYR] there is set analysis used within the set analysis. "$(=max({1} [FISCALYR]))" means Pick the maximum FISCALYR and ignore all selections. {1} is ignoring all selections.

so it meas: show the sum of the service issue count for the maximum fiscalyear, while ignoring month selections .

"$(=max({1} FISCALYR)-1)" is the same but max year - 1.

Re: Help with Set Expression

sunny_talwar — Fri, 08 Jun 2018 11:44:15 GMT

The bold part of the expression is just saying that ignore any selections made in those two fields. So, if you select FISCALMO 3, the values that the expression is showing won't change (compared to if you have not selected anything)

Sum({<FISCALMO=,[Calendar Fiscal Month]=,[FISCALYR]={"$(=max({1} [FISCALYR]))"}>}SERVICE_ISSUE_COUNT)

The above can also be written like this (without the equal sign) and means the same exact thing

Sum({<FISCALMO, [Calendar Fiscal Month], [FISCALYR]={"$(=max({1} [FISCALYR]))"}>}SERVICE_ISSUE_COUNT)

Re: Help with Set Expression

schneider2037 — Fri, 08 Jun 2018 11:47:26 GMT

Thank you! The documentation is outstanding, and your explanation spot on.

On an aside, it seems a rather cumbersome way to achieve the objective, but as it is working, not going to try and fix it!

~Jonathan

Re: Help with Set Expression

schneider2037 — Fri, 08 Jun 2018 11:49:11 GMT

Sunny - thanks! I was just thinking "well THIS is an odd way to achieve the objective."

Re: Help with Set Expression

sunny_talwar — Fri, 08 Jun 2018 11:51:01 GMT

"well THIS is an odd way to achieve the objective."

How else can achieve the same output? I mean if you always want to see the whole year for one object and then the other one should show based on selection in FISCALMO... how would you do it?

Re: Help with Set Expression

schneider2037 — Fri, 08 Jun 2018 12:04:33 GMT

Meant more along the lines of the syntax used (your version is a lot cleaner to read in my mind).

These set expressions are still a bit beyond my comprehension. Thanks again for the assist!

Re: Help with Set Expression

sunny_talwar — Fri, 08 Jun 2018 12:08:43 GMT

Meant more along the lines of the syntax used (your version is a lot cleaner to read in my mind).

Gotchya!! Well, I rarely use the equal sign, but I know a lot of people use it with the equal sign. Not sure if what I use is by design or a bug that Qlik never fixed it...

These set expressions are still a bit beyond my comprehension.

Isn't that true for syntax of any new language you are trying to learn. But I am sure you will get a hang of it.

Best,

Sunny