topic Re: Average of Delta's in QlikView

Average of Delta's

Mon, 26 Oct 2015 15:56:22 GMT

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)

Value	Delta	Average Delta
1	-	2
4	3	2
3	1	2
6	3	2
7	1	2
5	2	2
3	2	2

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

marcus_sommer — Mon, 26 Oct 2015 16:13:00 GMT

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

MarcoWedel — Mon, 26 Oct 2015 22:08:41 GMT

Hi,

one solution quite similar to yours could be:

hope this helps

regards

Marco

Re: Average of Delta's

Tue, 27 Oct 2015 08:42:18 GMT

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

Tue, 27 Oct 2015 08:47:40 GMT

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

marcus_sommer — Tue, 27 Oct 2015 16:18:44 GMT

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

swuehl — Tue, 27 Oct 2015 16:26:20 GMT

Try

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

Re: Average of Delta's

MarcoWedel — Tue, 27 Oct 2015 21:11:16 GMT

Hi,

maybe like this:

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

hope this helps

regards

Marco

Re: Average of Delta's

Wed, 28 Oct 2015 10:55:09 GMT

Thanks Marco,

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

Additionally I learned from your answer about getting the dimension dynamically with `[$(=Dimension)]`. This will come in handy in future projects.

Regards,

Jorrit

Re: Average of Delta's

Wed, 28 Oct 2015 10:58:37 GMT

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

Wed, 28 Oct 2015 10:59:42 GMT

Hello Marcus,

You and 2 others gave different correct answers.

Thanks for the help, It is much appreciated.

Re: Average of Delta's

swuehl — Wed, 28 Oct 2015 12:41:34 GMT

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

Wed, 23 Dec 2015 14:55:10 GMT

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

MarcoWedel — Wed, 23 Dec 2015 23:15:12 GMT

Hi,

Please don't post questions in already closed threads.

Instead you can open a new thread for your questions.

thanks

regards

Marco