16 Replies Latest reply: Mar 31, 2016 10:50 AM by Antoan Tenev RSS

    Creating average from "above" created expression in a chart

    Antoan Tenev

      Hi All,

       

      I was trying to build this for 4 hours and I failed miserably. Basically what I am trying to do is:

       

      Imagine that you have set of time constraints (that can vary - but I can easily fix that). For the sake of the example, let them be weeks - that are the dimension of the chart = week1, week 2 etc.I am creating the chart from a dataset that looks like below:

       

      Week number Volume Done

      1                    100

      2                    123

      3                    93

      4                    97

       

      What also I did is I created a line (the chart was bar + line, when bar shows the volume in total) that shows the movement in the volume shown in percentage - for example - in week 2 we have 23 % increase compared to week 1 and in week 3 we have around 30 % decrease compared tpo week 2) etc.

      I used similar expression in the chart = sum(Volume)/above(sum(volume)) -1 and it works fine.

      However i am failing to achieve an AVERAGE movement per week. For example if we have -15%, + 5% - 10%, I want to get them all in Positive % and divide them by 3 - simple as that - it should show 10% if we stick to the 3 numbers shown above.

       

      I am trying to show this average percentage either in a text inside the chart or in a textbox outside of the chart. If I am using straight table and if I use the average total it works but it does not show accurate values (when  aggr and avg used) inside the chart or in a textbox (same formula used) - it only provides accurate numbers in a straight table (average total).

       

      Please can you help as this is driving me crazy