Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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.
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.
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)
Thanks Henri. Your "simple" formula gives the same result then my complexe formula.
Well done !
JF