Hello together,
I am trying to create a rolling months chart showing the biggest as well as the smalles value.
Rolling months means that we have a date and value range from May 2011 to April 2013.
Period Year | Value | 12 mr value | Example |
May-11 | 83.1 |
|
|
Jun-11 | 52.1 |
|
|
Jul-11 | 51.6 |
|
|
Aug-11 | 61.7 |
|
|
Sep-11 | 54.6 |
|
|
Oct-11 | 66.1 |
|
|
Nov-11 | 45.6 |
|
|
Dec-11 | 9 |
|
|
Jan-12 | 73.1 |
|
|
Feb-12 | 36.7 |
|
|
Mar-12 | 76 |
|
|
Apr-12 | 77.1 |
|
|
May-12 | 48.2 | 651.8 | =SUM(May 11 - Apr 12) |
Jun-12 | 76.5 | 676.2 | =SUM(Jun 11 - Jul 12) |
Jul-12 | 70.6 | 695.2 | =SUM(Jul 11 - Aug 13) |
Aug-12 | 13.3 | 646.8 | " |
Sep-12 | 72.1 | 664.3 | " |
Oct-12 | 14.3 | 612.5 | " |
Nov-12 | 59.3 | 626.2 | " |
Dec-12 | 64.8 | 682 | " |
Jan-13 | 37.2 | 646.1 | " |
Feb-13 | 6.5 | 615.9 | " |
Mar-13 | 94.8 | 634.7 | " |
Apr-13 | 4.7 | 562.3 | =SUM(May 12 - Apr 13) |
My problem is now, how to set up a chart showing the the values in column 3 from May 2012 to April 2013.
I have set up this expression:
=SUM({<
[Measures (Key)]={'Operating Profit (EBIT)'},
[Version single (Key)]={'Actual'},
Period
>}Values)/1000000
including the function "Accumulation for 12 steps back".
To this point all existing Period Year entries are shown within the chart.
To get rid of this I have set a start and end value within the axes tab (Scale).
This seems to be fine as long as I use only line charts and no bar charts.
Furthermore I would like to identify the highest and smallest value.
Normally I would use the rank function to identify it but it could be possible, that rank=1 is located in the results I have supressed by setting a min/max scalling within the axes tab of the chart properties.
And now I have no ideas left how to set up this chart in a useful way.
Any suggestions?