13 Replies Latest reply: Dec 23, 2015 6:15 PM by Marco Wedel

# 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

• ###### Re: Average of Delta's

For this you need a range-function and the optional parameters from the above-function, something like this:

rangeavg(above(Delta, 0, rowno(total)))

- Marcus

• ###### Re: Average of Delta's

Hello Marcus,

I've tried your solution in attached qvw. It is definitely a step close than what I achieved so far but there is still 1 issue with it:

The expression averages the delta's for all rows above and including the current row, but I am looking for the average of all the rows (meaning the result is similar for all rows)

I looked into the help files for RangeAvg() and RowNo() but could not find how to make the expression take all rows (instead of only current + previous) into account.

I hope you can help me again.

Regards, Jorrit

• ###### Re: Average of Delta's

In this case might be a complete different approach helpful, like:

sum(total aggr(FABS(SUM(Value) - ABOVE (SUM(Value))), [Dimension 1])) /

(count(distinct total [Dimension 1]) - 1)

- 1 is manually because the first row will always fail but I think it could be included within the expression as condition. Further helpful could be: Average – Which average?

- Marcus

• ###### Re: Average of Delta's

Hello Marcus,

You and 2 others gave different correct answers.

Thanks for the help, It is much appreciated.

• ###### Re: Average of Delta's

Try

=RangeAvg(Above(Delta, 0, RowNo(total)), Below(Delta,1, NoOfRows() ))

• ###### Re: Average of Delta's

Thanks,

You and 2 others gave the correct answer.

I like yours because I think it´s elegant to solve this using just in Chart Inter Record Functions.

Regards,

• ###### Re: Average of Delta's

Hi,

one solution quite similar to yours could be:

hope this helps

regards

Marco

• ###### Re: Average of Delta's

Hello Marco,

I am using dynamic dimensions in my qvw. your solution gives me the the correct average delta on the lowest aggregation level, but I would not know how to implement it in a dataset with multiple aggregations.

In my example qvw (see attached) I'm using only 2 dimensions, but in real live there are going to be 15 to 20 different time levels and other groupings like by customer, by supplier, by location, etc.

Could you advise a way of doing the aggr() function without having to add an "ID" field?

Regards,

Jorrit

• ###### Re: Average of Delta's

Hi,

maybe like this:

```=Avg(TOTAL Aggr(fabs(Sum(Value)-Above(Sum(Value))),[\$(=Dimension)]))
```

hope this helps

regards

Marco

• ###### Re: Average of Delta's

Thanks Marco,

You and 2 others have given me a correct answer with which I can move forward.

Regards,

Jorrit

• ###### Re: Average of Delta's

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.

• ###### Re: Average of Delta's

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

• ###### Re: Average of Delta's

Hi,