Advanced Set Analysis

[michael84]

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

[rubenmarin]

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.

[michael84]

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!

[rubenmarin]

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.

[michael84]

Hi

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.

[rubenmarin · Accepted solution]

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)

[michael84]

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

Many Thanks