Simplify formula for relative bar chart using set analysis

[PWJ67]

Hi all,

In order to calculate the values (%) for a relative bar chart, the only way i've found is this complexe formula :

sum({$<STATTYPE1={S_AGE}, STATTYPE3={NB} , STATLIB1-={NR}, STATLIB2-={NR},STATTYPE2 = {DDC} , "=num#(STATLIB2)"={">2010"} >}STATVAL)
/
sum({$<STATTYPE1={S_AGE}, STATTYPE3={NB} , STATLIB1-={NR}, STATLIB2-={NR},STATTYPE2 = {DDC} , "=num#(STATLIB2)"={">2010"} >}
aggr(sum({$<STATTYPE1={S_AGE}, STATTYPE3={NB} , STATLIB1-={NR}, STATLIB2-={NR},STATTYPE2 = {DDC} , "=num#(STATLIB2)"={">2010"} >} total <STATLIB2> STATVAL),STATLIB2, STATLIB1))

With a simple "total <STATLIB2>" , it were note possible  to succeed because the result were allways the total global for the dimension STATLIB2, as I want the total by STATLIB2. 

STATLIB2 is a year in this case.

So could it be possible to simplify this formula ?

[henrikalmen · Accepted solution]

Since recently you could move the set analysis part outside of the calculations, at least it makes the expression a bit easier to read. I believe this should work:

{$<STATTYPE1={S_AGE}, STATTYPE3={NB} , STATLIB1-={NR}, STATLIB2-={NR},STATTYPE2 = {DDC} , "=num#(STATLIB2)"={">2010"} >} sum(STATVAL) / sum(aggr(sum(total <STATLIB2> STATVAL), STATLIB2, STATLIB1))

See https://community.qlik.com/t5/Design/New-Set-Analysis-syntax/ba-p/1983110 for more info.

[vinieme12]

just this should work,

The  Inner Sum in denominator is already a TOTAL at STATLIB2 level, 

sum({$<STATTYPE1={S_AGE}, STATTYPE3={NB} , STATLIB1-={NR}, STATLIB2-={NR},STATTYPE2 = {DDC} , "=num#(STATLIB2)"={">2010"} >}STATVAL)
/
sum({$<STATTYPE1={S_AGE}, STATTYPE3={NB} , STATLIB1-={NR}, STATLIB2-={NR},STATTYPE2 = {DDC} , "=num#(STATLIB2)"={">2010"} >} total <STATLIB2> STATVAL)

[PWJ67]

Thanks Henri. Your "simple" formula gives the same result then my complexe formula.

Well done !

JF