Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
PWJ67
Contributor II
Contributor II

Simplify formula for relative bar chart using set analysis

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 ?

 

Labels (3)
1 Solution

Accepted Solutions
henrikalmen
Specialist
Specialist

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.

View solution in original post

3 Replies
henrikalmen
Specialist
Specialist

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
Champion III
Champion III

 

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)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
PWJ67
Contributor II
Contributor II
Author

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

Well done !

JF