
Re: Average of Delta's
Marcus Sommer Oct 26, 2015 12:13 PM (in response to Jorrit van den Broeck)For this you need a rangefunction and the optional parameters from the abovefunction, something like this:
rangeavg(above(Delta, 0, rowno(total)))
 Marcus

Re: Average of Delta's
Jorrit van den Broeck Oct 27, 2015 4:54 AM (in response to Marcus Sommer )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

Average of Delta.qvw 158.8 K

Re: Average of Delta's
Marcus Sommer Oct 27, 2015 12:18 PM (in response to Jorrit van den Broeck)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
Jorrit van den Broeck Oct 28, 2015 6:59 AM (in response to Marcus Sommer )Hello Marcus,
You and 2 others gave different correct answers.
Thanks for the help, It is much appreciated.


Re: Average of Delta's
Stefan Wühl Oct 27, 2015 12:26 PM (in response to Jorrit van den Broeck)Try
=RangeAvg(Above(Delta, 0, RowNo(total)), Below(Delta,1, NoOfRows() ))

Average of Delta_2.qvw 156.0 K

Re: Average of Delta's
Jorrit van den Broeck Oct 28, 2015 6:58 AM (in response to Stefan Wühl )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
Marco Wedel Oct 26, 2015 6:08 PM (in response to Jorrit van den Broeck)
QlikCommunity_Thread_189108.qvw 150.0 K

Re: Average of Delta's
Jorrit van den Broeck Oct 27, 2015 4:47 AM (in response to Marco Wedel )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

Average of Delta.qvw 158.8 K

Re: Average of Delta's
Marco Wedel Oct 27, 2015 5:11 PM (in response to Jorrit van den Broeck)Hi,
maybe like this:
=Avg(TOTAL Aggr(fabs(Sum(Value)Above(Sum(Value))),[$(=Dimension)]))
hope this helps
regards
Marco

Re: Average of Delta's
Jorrit van den Broeck Oct 28, 2015 6:55 AM (in response to Marco Wedel )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
Stefan Wühl Oct 28, 2015 8:41 AM (in response to Marco Wedel )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
Andy Bross Dec 23, 2015 9:55 AM (in response to Marco Wedel )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
Marco Wedel Dec 23, 2015 6:15 PM (in response to Andy Bross)Hi,
Please don't post questions in already closed threads.
Instead you can open a new thread for your questions.
thanks
regards
Marco



