Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Nags
Partner - Contributor III
Partner - Contributor III

Calculating percentage in bar chart by TOTAL for each dimension

Qlik Sense experts,

Need your help to resolve this issue. I am using Qlik Sense February 2020

I have an expression: 

sum(if(TIME_OFF_BUCKET='WORK', DURATION_HOURS))

sum(TOTAL<[DATE_PAY_SCHED.autoCalendar.Date]> IF(TIME_OFF_BUCKET='WORK',DURATION_HOURS)) 

with this expression, I see the view as given in below screen1.

But, I would like to see the percentage as screen2.

Thanks in advance.

 

1 Solution

Accepted Solutions
Nags
Partner - Contributor III
Partner - Contributor III
Author

Thank you all for looking for resolution. After some research, I got the solution. Both solutions are working fine:

Solution - 1:

sum({<TIME_OFF_BUCKET={'WORK'}>} DURATION_HOURS)
/
aggr(nodistinct sum( {<TIME_OFF_BUCKET={'WORK'}>} DURATION_HOURS ), [DATE_PAY_SCHED.autoCalendar.Date])

 

Solution - 2:

sum({<TIME_OFF_BUCKET={'WORK'}>} DURATION_HOURS)
/
aggr(sum(TOTAL<[DATE_PAY_SCHED.autoCalendar.Date]> {<TIME_OFF_BUCKET={'WORK'}>} DURATION_HOURS ), [DATE_PAY_SCHED.autoCalendar.Date], RVP)

View solution in original post

3 Replies
Gui_Approbato
Partner - Creator III
Partner - Creator III

Hello,

Did you try it with a set expression instead of IF ?

It would be like:

 

sum({<TIME_OFF_BUCKET={'WORK'}>}  DURATION_HOURS)

sum(TOTAL<[DATE_PAY_SCHED.autoCalendar.Date]>  {<TIME_OFF_BUCKET={'WORK'}>}  DURATION_HOURS )

 

 

Nags
Partner - Contributor III
Partner - Contributor III
Author

Thanks for your response Gui_Approbato!

I tried with the set expression earlier (with the same expression as your have mentioned), the bar chart results are same as given in screen1.

When I select one specific Schedule Date (DATE_PAY_SCHED), then the % shows correct in the bar chart (as shown in screen2). I assume the TOTAL <DATE_PAY_SCHED> is not grouping the work_hours properly. It is sum up all the days, which is the problem.

Nags
Partner - Contributor III
Partner - Contributor III
Author

Thank you all for looking for resolution. After some research, I got the solution. Both solutions are working fine:

Solution - 1:

sum({<TIME_OFF_BUCKET={'WORK'}>} DURATION_HOURS)
/
aggr(nodistinct sum( {<TIME_OFF_BUCKET={'WORK'}>} DURATION_HOURS ), [DATE_PAY_SCHED.autoCalendar.Date])

 

Solution - 2:

sum({<TIME_OFF_BUCKET={'WORK'}>} DURATION_HOURS)
/
aggr(sum(TOTAL<[DATE_PAY_SCHED.autoCalendar.Date]> {<TIME_OFF_BUCKET={'WORK'}>} DURATION_HOURS ), [DATE_PAY_SCHED.autoCalendar.Date], RVP)