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

    Average of Delta's



      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


      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

          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
            Marco Wedel



            one solution quite similar to yours could be:





            hope this helps





              • 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?





                  • Re: Average of Delta's
                    Marco Wedel



                    maybe like this:


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








                    hope this helps





                      • Re: Average of Delta's

                        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.





                        • Re: Average of Delta's
                          Stefan Wühl



                          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

                            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).


                            Avg. Bill Rate Current week  - "That" week's Avg Bill Rate = Delta

                            100% - (Delta / Current Week Bill Rate)


                            Value from Expression1 * B2NB Ratio


                            Thanks so much for any specific suggestions