Delta expressions

Thu, 24 Dec 2015 13:29:57 GMT

Hi all - I noticed some already closed threads that discussed this type of need, but none that fit into my scenario. Can anyone suggest the best expression(s) to accomplish this?

* I am calculating a Billable FTE to NonBillable Ratio with the following formula: (sum([Billable Hrs]) / 40) / (count(distinct {<[BillableFlag]-= {'0'}>} Resource)) --> For easy math, lets assume this results in 20 / 10 = 2.0 as the billable to nonbillable ratio [B2NB] for 1 given period.

* I then want to factor in the avg Billing rate by doing: sum([Billable Hrs] * [Billing Rate]) / sum([Billable Hrs]) --> For easy math for one week time period, lets assume this results in $180 avg billing rate.

* I can plot these as two expressions but its not telling a good story.

My Dimension is Week, and I want to able to show the adjusted billing rate against the Billable to NonBillable ratio [B2NB], leveraging the deltas between all Week dimensions.

So for example:

on 1/1/2014, the B2NB was 2.0 and the Avg. Bill Rate was $140

on 1/1/2015, the B2NB was 2.0 and the Avg. Bill Rate was $180

note: my chart would show 52 dates (1 per week) per year, I am just explaining 1 specific delta.

The requirement provided to me is: (but I am hoping the community can either help with the correct syntax or offer a better idea).

Expression1:

Avg. Bill Rate Current week - "That" week's Avg Bill Rate = Delta (note: negative's can/should be possible)

100% - (Delta / Current Week Bill Rate)

Expression2:

Value from Expression1 * B2NB Ratio

Thanks so much for any specific suggestions

Re: Delta expressions

Peter_Cammaert — Thu, 24 Dec 2015 13:59:36 GMT

I apologise for maybe not understanding the simplest of reasonings. But why calculate B2NB this way? The B2NB-ratio usually ranges from 0 to 1. A ratio of 2.0 can be either:

everyone is doing a lot of overtime, or
50% is doing even more overtime, and 50% is "on holiday".

Is this a good performance indicator?

Peter

topic Delta expressions in QlikView

Delta expressions

Re: Delta expressions