topic Problem using variable inside another complex aggregation formula in QlikView

Problem using variable inside another complex aggregation formula

Sat, 19 Mar 2016 20:48:32 GMT

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,

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

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

swuehl — Sat, 19 Mar 2016 22:57:28 GMT

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

sunny_talwar — Sat, 19 Mar 2016 23:14:33 GMT

Would it possible to share a sample?

Re: Problem using variable inside another complex aggregation formula

Sun, 20 Mar 2016 22:45:20 GMT

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

sunny_talwar — Mon, 21 Mar 2016 09:22:12 GMT

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

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

Mon, 21 Mar 2016 10:44:19 GMT

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