Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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?

1 Solution

Accepted Solutions

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


Capture.PNG

5 Replies
MVP
MVP

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

Would it possible to share a sample?

Not applicable

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


Capture.PNG

Not applicable

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

Community Browser