Skip to Content

As part of my hidden gems series I want to share with you the capability of having an average function plotted inside my visuals.

Hold on (with gasps of shock) – isn’t this minor feature already inside the product?

Well yes and no.

Lumira has a few average options (no , I don’t mean that to be a slur on Lumira) however Lumira doesn’t have the one that I want to use.

Let me explain using an example;

/wp-content/uploads/2015/04/1_688301.png

The screenshot above shows sales plotted across days.  What I want is another line (a straight line) that shows the average for all of the data plotted.  Using my data I calculate that the average is 36.6 or 37 rounded up – the sum of the sales (696) divided by the total number of days (19).

Like I mentioned earlier there are some average options available to me however  neither giving me the answer that I want.  Let me run through these options below.

1. I can create another measure based upon sales and change the aggregation option to Average (see screenshot below).

/wp-content/uploads/2015/04/2_688302.png

However when I use this, the line plotted is the same as my original sales line (see screenshot below).

/wp-content/uploads/2015/04/3_688330.png

2. My next option involved using one of the average options available when using the Cog icon on the Y Axis for my measure Sales (see screenshot below).

/wp-content/uploads/2015/04/4_688391.png

Here I have Running Average and Moving Average options available to me.  If I use the Running Average option, again I don’t get the desired result (see screenshot below) and the moving average option will just smooth my data (this one though is more interesting – we will come back to this later on in the blog).

/wp-content/uploads/2015/04/5_688392.png

3. Without going through the effort of trying the variety of average functions available surely I could have simply created a calculation:

Avg = Sales (with a sum aggregation) divided by a Sales (create a new measure but change the aggregation to Count All)

/wp-content/uploads/2015/04/6_688393.png

Unfortunately not.   This just plots a line over the top of my original line – the same result as option 1.

So after all of my efforts and writing what is the answer?

Well, I hinted about the answer in option 2 – moving average.  A moving average function is used to smooth the data.  Used on its own I won’t get my answer, however Lumira has an extra option called “Include Self” which will help me with my result.

/wp-content/uploads/2015/04/7_688400.png

As long as I check this box I eventually get to the answer that I want to see (see screenshot below):

/wp-content/uploads/2015/04/8_688401.png

(With my average being rounded up to 37).

The “Include Self” option is checked if you want the first month to be included in the calculation.

At last I can see my sales with a straight line average plotted across.

I would love to have come up with this answer, however my thanks goes out to another SAP colleague.

Finally, after writing this blog, I cannot believe that I have spent so much effort writing about an average calculation.

Hope that this helps.

Dean

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Balareddy Krishnareddy Balakrishna

    Hi,

    Thanks for this simple yet complicated function usage with great screen shots and examples.  I had a similar problem in the past and didnt check the ‘include self’.  This solves my long pending average calcuation 🙂

    Bala.

    (0) 
  2. Andrew Mathewes

    This is wonderful and definitely solved the first step of my problem. Thank you!

    I guess what I’m wondering is this, though: if this is used in a report that I’m generating every week, each week adding another line of data, am I going to have to edit the calculation every week to keep it a straight line? Right now, I’ve just put the largest number the field will hold in hopes that that is a sustainable solution…

    Second question: this is only step one of the solution to my problem, as my real goal is to build a control chart, which means I need not just this moving average line but also +/- control lines (based on one standard deviation).

    Any thoughts? Am I just wrong to hope that Lumira can be the tool that I use for this?

    (0) 
    1. Matt F

      Hi Andrew,
      Did you find out if Lumira is the correct solution for your use case? If so, could you point me in the right direction?

      Thanks,
      Matt

      (0) 

Leave a Reply