topic Re: Set Analysis - Nested Formula not working in App Development

Set Analysis - Nested Formula not working

alliedarmour — Fri, 18 Aug 2023 08:27:11 GMT

Hey Qlikkers,

I have a set expression which should count medical cases based on some conditions and over several years - out of that, I wanna make a sparkline in my table later.

The "easy" version of the formular works:

Count(
{$<Leistungskatalog = {"*"},
BewegungJahr = {">=$(vMinYear)<=$(vMaxYear)"}, BewegungMonat = , BewegungQuartal = >}
DISTINCT Fallnummer)

Now if I add another filter which should filter the cases by the fact if they received at least one billable treatment, the formula suddenly returns 0 for every dimension:

Count(
{$<Leistungskatalog = {"*"},
BewegungJahr = {">=$(vMinYear)<=$(vMaxYear)"}, FallBewegungKey = {"=Count({$<BewegungJahr = {>=$(vMinYear)<=$(vMaxYear)}>} ICD) > 0"} >}
DISTINCT Fallnummer)

EDIT: If I insert a single year inside the Count set expression (like {"=Count({$<BewegungJahr = {2021}}) then it works without problems - so the issue here seems to be the variable timespan.

Any idea what I'm doing wrong here?

Thanks for any help!

Re: Set Analysis - Nested Formula not working

pcv_devo — Thu, 17 Aug 2023 13:29:50 GMT

Hi @alliedarmour ,

The syntax you use to search seems to be fine. Can you provide more information about the output? Are you getting nulls or an erroneous value?

Remember that the Count of ICD respects the outer selection.

Re: Set Analysis - Nested Formula not working

marcus_sommer — Thu, 17 Aug 2023 16:11:33 GMT

It might be that you do not need the count and a simplified condition would be enough, like:

Count(
{$<Leistungskatalog = {"*"},
BewegungJahr = {">=$(vMinYear)<=$(vMaxYear)"}, ICD {"*"} >}
DISTINCT Fallnummer)

Re: Set Analysis - Nested Formula not working

alliedarmour — Fri, 18 Aug 2023 06:56:37 GMT

Hi @pcv_devo,

I'm just getting 0 (zero), not erroneous values - but I double checked the cases in a seperate table if there are any ICDs and there are - so they should've been included by the set expression.

Re: Set Analysis - Nested Formula not working

alliedarmour — Fri, 18 Aug 2023 08:18:41 GMT

Hey @marcus_sommer,

thanks for your reply - that should indeed work, but my goal is to understand why my expression (even if it is slightly more complex) doesn't work 😅 . It should give me the same result, but I want to really understand why it fails.

Re: Set Analysis - Nested Formula not working

alliedarmour — Fri, 18 Aug 2023 09:05:22 GMT

Okay, it looks like I found the error:

FallBewegungKey = {"=Count({<BewegungJahr = {"">=$(vMinYear)<=$(vMaxYear)""}>} [ICD-Code]) > 0"}

It looks like you need two pairs of double quotes so Qlik interpretes it as string correctly. I also tried with single quoted, but that wouldn't work - at least in my case.

Re: Set Analysis - Nested Formula not working

marcus_sommer — Fri, 18 Aug 2023 09:06:23 GMT

Within your count-condition the set analysis is missing the double-quotes wrapping - so it might be just a small syntax-issue.

If it's not the cause it would be quite difficult to say why it didn't returned your expected results - without a deeper view on the data and their relationship within the data-model and also the dimensional context in which the expression is performed.

Personally I suggest to avoid such logic because your inner count-condition isn't a real set analysis anymore because it means to perform a row-level evaluation while a classical set analysis is a column-level evaluation like a selection. It's more or less an if-loop in a set analysis syntax. It will work in many cases but without the performance-benefit which a classical set analysis has against an if-loop. More logically and often simpler would be to apply such kind of condition in beforehand or afterwards instead in the middle, for example with something like:

if(count() > X, MyExpression)

or maybe

MyExpression * -(count() > X)

Re: Set Analysis - Nested Formula not working

alliedarmour — Fri, 18 Aug 2023 09:22:26 GMT

Hey,

as I stated in last answer the missing double quotes were the issue indeed.

I'm quite new to Qlik so thanks a bunch for your explanation, that helps me a lot! Will consider this when refining the application 👍.

Have a nice weekend!