Relative + Absolute values in diagram - Sync left & right axis + 100% issue

[#1]

Dear all,

I need to draw a diagram like this:

grey bars: Accumulated absolute values

green line: Relative value of the same values

Basically I use this formula in the script to get the 100% value:

SET eMaxSoll=Sum(TOTAL  Aggr( Sum( $1 * vAnzahl_SollPF), %IWeek));

And this as expression for the diagram's green line:

=RangeSum(

Above(

Sum( [Config_Soll.Soll-Wert]*vAnzahl_SollPF),

0,

RowNo()

)

)/$(eMaxSoll([Config_Soll.Soll-Wert]))

The grey bars are the absolute accumulated values for the same… using this formula

=RangeSum(

Above(

Sum( [Config_Soll.Soll-Wert]*vAnzahl_SollPF),

0,

RowNo()

)

)

The diagram above only differs in vAnzahl_SollPF=2 setting and looks perfect.

2 QUESTIONS:

1: My problem is when I set vAnzahl_SollPF=1 I get this curve, which ends up at 112% instead of 100% and I have no clue why?

In other cases it ends up lower than 100%... Why?

What will be the correct formulas to always end at 100% ?

2: Is there a way to always synchronize left and right scale so that 100% always is on the same hight as the max. absolute even when having other higher bars in the diagram like this (I would like the green line to end at 100% = top of rightmost grey bar = 5600 - no matter how high the right scale goes (driven by blue bars...)

Thanks for your help!

Oliver

[marcus_sommer]

It's not quite clear but I would rather use this as eMaxSoll:

Sum( TOTAL [Config_Soll.Soll-Wert]*vAnzahl_SollPF)

instead of the aggr-function.

- Marcus

[#3]

Hi Marcus

thanks for your answer - unfortunately it does not work out:

See e.g. In this example:

=$(eMaxSoll([Config_Soll_km.Soll-Wert]))    RETURNS 3200
Sum( TOTAL [Config_Soll_km.Soll-Wert]*vAnzahl_SollPF)  RETURNS 1600

THE CORRECT VALUE WOULD BE 3800...

Any other idea?

[marcus_sommer]

Please provide a small example-app with a few inline-tables which demonstrate this problem.

- Marcus

[#5]

due to company policies I had to reduce data sets and "anonymize" them. Does this QVW work for you - you only cannot do a reload, but work with the data already loaded.

Thanks a lot

Oliver

[marcus_sommer · Accepted solution]

It's quite difficult to understand your data and what do you want to achieve but you need to consider your data-quality (there are some missing values respectively NULL between your used dimensions and the measure - how need this to be considered?) and possible selections. In your case returned the variable eMaxSoll not the max. value of your normal sum-expression which results in not getting the 100%. I have the variable extended to:

Sum(TOTAL  Aggr( Sum({< %IWeek = p(%IWeek), IStufenWk.MS = p(IStufenWk.MS)>}[Config_Soll_km.Soll-Wert] * vAnzahl_SollPF), %IWeek, IStufenWk.MS))

and it worked for your reduced datamodel and I think you could adapt it if you extend more complexity:

- Marcus

[#7]

Hi Marcus,

thanks a lot for your great help!!!! - even though I don't understand yet the formula - it does work also with my complete data model!!!!! 🙂

Best regards Oliver