topic Re: Advanced Set Analysis in App Development

Advanced Set Analysis

michael84 — Mon, 08 Nov 2021 10:05:28 GMT

Hello

I got a problem in syntax writing in a complex set analysis. For some reason i need to select a set of accounts that depends on the sum of values of certain years. The Formula how its written below works perfectly but it's not what i need at the end.

The Problem is the definition for the account, here I wrote "Year= {$(vJahr)}" in fact I need the same as before "Year = {"<=$(vYear)" so that all values smaller than "vJahr" are accumulated. I tried several possibilities how i could write Year smaller the vJahr, but it won't work. As soon as i use smaller than, the formula does not calculate. Of course i could write a single variable for each year, which would work. It's just not a very elegant nor clearly represented way of writing.

SUM({< Year= {"<=$(vJahr)"}, Account= {"=(SUM( {<Year= {$(vJahr)}>} VALUE)) > 0"}>} VALUE) //What I need (but doesn't work): SUM({< Year= {"<=$(vJahr)"}, Account= {"=(SUM( {<Year= {'<='$(vJahr)}>} VALUE)) > 0"}>} VALUE)

Info: vJahr ist defined in the script as followed: vJahr = CHR(36) & '(=MAX(GJAHR))' --> CHR(36) = "$"

Further I tried to write the definiton in an other variable using the CHR() function, for example I defined a new Variable "vTest" and wrote as followed:

=CHR(34)&CHR(36)&'(='&CHR(39)& '<='& CHR(39) & '&' & vJahr &')'&CHR(34) //Result: =vTest: "$(='<='&$(=MAX(GJAHR)))"

Also this istn't working.

Is it possible, that QlikView in this case can't handle the small than (<=) in the "second" set anlalysis? For me its only important how to write the "<=" in the second part of the set analysis, what ist the correct sytax?

thanks in advance for your help!

Best regard

Michael

Re: Advanced Set Analysis

rubenmarin — Mon, 08 Nov 2021 10:36:58 GMT

Hi, if you define vJahr just as =MAX(GJAHR) it should work.

If doesn't works you can also confirm that =MAX(GJAHR) returns the value you expect, then try to edit your initial expression and instead of $(vJahr) write the number of the year to check if it works as expected.

Re: Advanced Set Analysis

michael84 — Mon, 08 Nov 2021 15:37:17 GMT

Hi,

I tried it in a diffferent ways, with 6 and also with a number (f.e. 2020) but non worked.

Here the statement that works, when vJahr ist defined as $(=MAX(GJAHR))

SUM({< Year= {"<=$(vJahr)"}, Account= {"=(SUM( {<Year= {$(vJahr)}>} Value)) > 0"}>} Value)

If I define a new Variable f.e. vJahrTest and write as followed:

vJahrTest =CHR(34)&'(='&CHR(39)& '<='& CHR(39) & '&' & (vJahr) &')'&CHR(34)

this returns
"(='<='&(=MAX(GJAHR)))", does not work (with or without $.

If I replace (vJahr) with 2020 (--> "(='<='2020)" or if I add a "$" doesn't change a thing.

As soon as there is "<=" its not working. Is it possible that Qlik really can't handle this set analysis in an existing set analysis?

Thanks for your help!

Re: Advanced Set Analysis

rubenmarin — Tue, 09 Nov 2021 06:52:47 GMT

Hi, if this works:

SUM({< Year= {"<=2020"}, Account= {"=(SUM( {<Year= {2020}>} Value)) > 0"}>} Value)

Just using =MAX(GJAHR) as the content for vJahr should work (the equal sign i'ts part of the variable content).

Have you tested what I said previously? Why/What are you trying to use vJahrTest? vJahrTest =CHR(34)&Chr(36)&'(='&CHR(39)& '<='& CHR(39) & '&' &Chr(36)& '(vJahr)'&CHR(34)

If you open the expression editor, on the bar below you can see how Qlik it's expanding Values.

The $( only means that it needs to solve expression inside each $( and once everything it's expanded it calculates the expression.

Re: Advanced Set Analysis

michael84 — Wed, 10 Nov 2021 12:17:58 GMT

Maybe i didn't discribed the problem properly. What I need ist the follwoing formula:

SUM({< Year= {"<=$(vJahr)"}, Account= {"=SUM( {<Year= {"<=$(vJahr)"}>} Value) > 0"}>} Value)

The failure in this formula is red marked:

SUM({< Year= {"<=$(vJahr)"}, Account= {"=SUM( {<Year= {"<=$(vJahr)"}>} Value) > 0"}>} Value)

It must be smaller or bigger than so I can't let the "<" or ">" away, but exactly these two operators are the problem.

Maybe this might help to solve the problem...

Thanks again for helping me.

Re: Advanced Set Analysis

rubenmarin — Wed, 10 Nov 2021 12:45:25 GMT

Hi, I think your issue here is that the double quotes, the first double quote of "<=$(vJahr)" is closing the "=SUM double quote, try to use double-double quotes to avoid this:

=SUM({< Year= {"<=$(vJahr)"}, Account= {"=SUM( {<Year= {""<=$(vJahr)""}>} Value) > 0"}>} Value)

I tested with this data

LOAD * INLINE [ Year, Account, Value 2019,1,2 2020,1,5 2021,1,-10 2019,2,3 2020,2,7 2021,2,5 ];

and vJahr as

=Max(Year)

If I select 2020 it returns 17 (7 for account 1 and 10 for account 2), if i select 2021 or nothing it returns 15 (from account 1)

Re: Advanced Set Analysis

michael84 — Wed, 10 Nov 2021 15:09:52 GMT

Perfect, the correct solution is the double-double quote!

Many Thanks