topic Re: Showing percentage point on a line graph in QlikView

Showing percentage point on a line graph

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have a straight table to show utilization based off of Hours Used/Hours Available for a client center. The business rule is to count 6hrs per business day. However, there are days when meeting go over that total daily 6hrs limit which pushes some utilization past the 100% so I've put in a conditional statement to always keep my number at 100% max (if ((Sum(If((Hour([Reserved End])- Hour([Reserved Start])) < 0 , 24 + (Hour([Reserved End])- Hour([Reserved Start])), (Hour([Reserved End])- Hour([Reserved Start])))) / (BusinessDays*6))>1, 1, (Sum(If((Hour([Reserved End])- Hour([Reserved Start])) < 0 , 24 + (Hour([Reserved End])- Hour([Reserved Start])), (Hour([Reserved End])- Hour([Reserved Start])))) / (BusinessDays*6))) ).

When I try this expression on a line graph with the dimension Month, Year, I get a "No data to show" message. I can get the lines if I just use (Sum(If((Hour([Reserved End])- Hour([Reserved Start])) < 0 , 24 + (Hour([Reserved End])- Hour([Reserved Start])), (Hour([Reserved End])- Hour([Reserved Start])))) / (BusinessDays*6)))) but that will not be accurate with my table.

I will really appreciate if anyone suggest a workaround or a variation of the expression that will get me the intended result. Thank you!

Re: Showing percentage point on a line graph

sunny_talwar — Tue, 18 Jul 2017 17:57:11 GMT

The expression which is working seems to have extra parenthesis... can you provide the exact expression as parenthesis could be an issue here

Sum(If(

(Hour([Reserved End])- Hour([Reserved Start])) < 0 ,

24 + (Hour([Reserved End])-Hour([Reserved Start])),

(Hour([Reserved End])- Hour([Reserved Start])))) / (BusinessDays*6)))

Re: Showing percentage point on a line graph

Oleg_Troyansky — Tue, 18 Jul 2017 18:00:55 GMT

If the word "max" belong to your formula, then you are trying to use nested aggregation without AGGR, and that is not allowed.

In order to limit your results by 100%, I'd suggest to use RangeMin:

RangeMin(mycalculation, 1) - this formula will select the lower between your calculation result and 100%.

Otherwise, if you post a small sample of your app, someone could suggest a better solution.

cheers,

Oleg Troyansky

Upgrade your Qlik skills with my book QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sense

Re: Showing percentage point on a line graph

Wed, 19 Jul 2017 17:47:26 GMT

Thank you for your reply Oleg.

I'm not using 'max' as part of the expression, that was just me explaining the context behind the expression. The expression starts from "(IF".I will try your suggestion.

Unfortunately, due to some strict policies at my org I won't be able to share anything in this forum.

Re: Showing percentage point on a line graph

Wed, 19 Jul 2017 17:52:37 GMT

Thanks for your reply Sunny.

These are the 3 expressions from my utilization table:

AvailBookingHrs: BusinessDays*6

TotalBookingHrs: Sum(If ((Hour([Reserved End])- Hour([Reserved Start])) < 0 , 24 + (Hour([Reserved End])- Hour([Reserved Start])), (Hour([Reserved End])- Hour([Reserved Start]))))

Utilization: if ((Sum(If((Hour([Reserved End])- Hour([Reserved Start])) < 0 , 24 + (Hour([Reserved End])- Hour([Reserved Start])), (Hour([Reserved End])- Hour([Reserved Start])))) / (BusinessDays*6))>1, 1, (Sum(If((Hour([Reserved End])- Hour([Reserved Start])) < 0 , 24 + (Hour([Reserved End])- Hour([Reserved Start])), (Hour([Reserved End])- Hour([Reserved Start])))) / (BusinessDays*6)))

And, I'm only enabling the Utilization expression in the line graph. Thanks.