Problem using variable inside another complex aggregation formula

[#1]

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?

[swuehl]

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.

[sunny_talwar]

Would it possible to share a sample?

[#4]

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

[sunny_talwar · Accepted solution]

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))

[#6]

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