5 Replies Latest reply: Mar 21, 2016 6:44 AM by Oliver Krause

# Problem using variable inside another complex aggregation formula

Dear all,

I define a formula using set analysis in my script:

SET eAnzahlSollPF = Max({<Config_AnzahlSollFzge.Produktlinie=Config_Produktlinie.Produktlinie, Config_AnzahlSollFzge.I_Stufe=%I_Stufe.NAME>} [Config_AnzahlSollFzge.Anzahl-Soll-PF]);

When I only put \$(eAnzahlSollPF) as expression in the chart, I get with my current selection 4:

and I need to use the above variable within THIS formula (when I replace the XXXXXX with a 4 I get the graph below, when putting the formula above I get no values):

=RangeSum(         Above(

[Config_Soll_LC.Soll-Wert]* XXXXXX,

0,

RowNo()

)

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

I get the graph I need:

But when replacing the 4 by \$(eAnzahlSollPF)

in this line

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

=RangeSum( //eSollKurveRel

Above(

[Config_Soll_LC.Soll-Wert]*\$(eAnzahlSollPF),

0,

RowNo()

)

)/Sum(TOTAL Aggr( Sum({< %IWeek = p(%IWeek), IStufenWk.MS = p(IStufenWk.MS)>} [Config_Soll_LC.Soll-Wert] * \$(eAnzahlSollPF)), %IWeek, IStufenWk.MS))

I only get this

I get no values...

Any idea what I do wrong?

• ###### Re: Problem using variable inside another complex aggregation formula

Your dollar sign expansion expands your variable into a Sum() aggregation function, hence the result is an aggregation embedded into another aggregation, which will generally not work.

It's hard to suggest something else without knowing the full context of this expression and your requirements. If possible, please upload a small sample QVW.

• ###### Re: Problem using variable inside another complex aggregation formula

Thanks, this would explain... please see attached the small sample with 2 graphs.

Left: The real one with the problem

Right: The one with a static "4" in the formula which works... but I don't need it static...

Thanks, Oliver

• ###### Re: Problem using variable inside another complex aggregation formula

Try with these variable definations:

RangeSum(

Above(

\$1*Max({<Config_AnzahlSollFzge.Produktlinie=Config_Produktlinie.Produktlinie, Config_AnzahlSollFzge.I_Stufe=%I_Stufe.NAME>} [Config_AnzahlSollFzge.Anzahl-Soll-PF]),

0,

RowNo()

)

)/Sum(TOTAL  Aggr( Sum({< %IWeek = p(%IWeek), IStufenWk.MS = p(IStufenWk.MS)>} \$1 * Max(TOTAL {<Config_AnzahlSollFzge.Produktlinie=Config_Produktlinie.Produktlinie, Config_AnzahlSollFzge.I_Stufe=%I_Stufe.NAME>} [Config_AnzahlSollFzge.Anzahl-Soll-PF])), %IWeek, IStufenWk.MS))

Sum(TOTAL  Aggr( Sum({< %IWeek = p(%IWeek), IStufenWk.MS = p(IStufenWk.MS)>} \$1 * Max(TOTAL {<Config_AnzahlSollFzge.Produktlinie=Config_Produktlinie.Produktlinie, Config_AnzahlSollFzge.I_Stufe=%I_Stufe.NAME>} [Config_AnzahlSollFzge.Anzahl-Soll-PF])), %IWeek, IStufenWk.MS))

• ###### Re: Problem using variable inside another complex aggregation formula

Hi Sunny,

thanks a lot... this indeed solved my problem. Solution looks so easy... but cost me a lot of headache before!!

Thanks again

Oliver

• ###### Re: Problem using variable inside another complex aggregation formula

Would it possible to share a sample?