topic Re: Calculating percentage in bar chart by TOTAL for each dimension in App Development

Calculating percentage in bar chart by TOTAL for each dimension

Nags — Fri, 26 Jun 2020 22:24:26 GMT

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.

Re: Calculating percentage in bar chart by TOTAL for each dimension

Gui_Approbato — Fri, 26 Jun 2020 22:54:10 GMT

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 )

Re: Calculating percentage in bar chart by TOTAL for each dimension

Nags — Sat, 27 Jun 2020 00:01:20 GMT

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.

Re: Calculating percentage in bar chart by TOTAL for each dimension

Nags — Sat, 27 Jun 2020 04:02:47 GMT

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)