Showing percentage point on a line graph

[#1]

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!

[sunny_talwar]

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)))

[Oleg_Troyansky]

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

[#4]

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.

[#5]

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.