Time Calculation

Mon, 01 Jun 2015 12:27:13 GMT

Hello Everyone,

I need to calculate points based on time criteria.

For example:

To resolve a ticket, the Resolution time of the ticket should not exceed 10 hours. If it does 10 points are conceded.

However, if the ticket exceed 20 hours 30 points are conceded.

To obtain the total time taken to resolve the ticket i use the following formula as I calculate just working hours and working time:

If(Date([Resolution time]) > 0, Floor(

(RangeMin(18/24, Frac(Date([Resolution time]) )) -

RangeMax(8/24, Frac(Date_Opening)) +

RangeMax((NetworkDays(Date_Opening, Date([Resolution time]) ) - 1) * (18/24 - 8/24), 0)) / 10)& ' day(s) ' &

Interval( Frac( (

RangeMin(18/24, Frac(Date(Resolution time]) )) -

RangeMax(8/24, Frac(Date_Opening)) +

RangeMax((NetworkDays(Date_Opening, Date(Resolution time]) ) - 1) * (18/24 - 8/24), 0) ) ), 'hh:mm'),0)

I do get the correct figures and my date and time are in the format : 0 day(s) 00:00 (named as [Total Time]).

Now i need to calculate the points based on these [Total Time].

So if [Total Time] > 10 Hours and [Total Time] < 20 Hours, then ([Total Time] - 10Hours )*10 Points and If([Total Time]>=20Hours, then ([Total Time]-20Hours)*20Points.

I did on excel and it works fine but on qlikview it is harder.

Does anyone knows how to solve this please?

Many Thanks,

Hasvine

Re: Time Calculation

sunny_talwar — Mon, 01 Jun 2015 12:38:36 GMT

Try this may be:

If([Total Time] < Interval#('20', 'hh'), ([Total Time] - Interval#('10', 'hh')) * 10, ([Total Time] - Interval#('20', 'hh')) * 20) as [Points Conceded]

topic Re: Time Calculation in QlikView

Time Calculation

Re: Time Calculation