Calculating percentage in bar chart by TOTAL for each dimension

[Nags]

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.

[Gui_Approbato]

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]

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 · Accepted solution]

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)