Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Average of Delta's

Hello,

This is going to be my first question on the Qlik Community. I've been searching fir 2 days now and cannot find a solution for this.

I have a where I use the ABOVE() to calculate the delta between a value and the previous one. Expression for Delta: =sqrt(sqr(Value-above(Value))).

Now I'm looking for a way to average these delta's like in this example: (in this example sum of delta is 12, divided by 6 steps between groups = 2)

ValueDeltaAverage Delta
1-2
432
312
632
712
522
322

This cannot be done in data load step because of multiple dynamic dimensions and expressions.

I've tried to solve it with AVG(TOTAL AGGR(SUM(Value) - ABOVE(SUM(Value)))) function but I don't get it to work in combination with ABOVE() function.

Any help is very welcome.

Regards, Jorrit

13 Replies
Not applicable
Author

Hello Marcus,

You and 2 others gave different correct answers.

Thanks for the help, It is much appreciated.

swuehl
MVP
MVP

Jorrit,

please note that using chart inter records like above() in advanced aggregation might return unexpected results, since advanced aggregation sorts dimension values always by load order, and your chart dimension values can be sorted differently, hence the Delta calculation in chart column  'Delta' and the Delta calculation within advanced aggregation may differ.

Not applicable
Author

Hi all - I have similar need that perhaps someone can assist.

* 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]

* 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 billing adjusted 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 example.

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

100% - (Delta / Current Week Bill Rate)

Expression2:

Value from Expression1 * B2NB Ratio

Thanks so much for any specific suggestions

MarcoWedel

Hi,

Please don't post questions in already closed threads.

Instead you can open a new thread for your questions.

thanks

regards

Marco