Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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 )
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.
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)