topic Re: Error in expression nested aggregation not allowed in QlikView

Error in expression nested aggregation not allowed

Fri, 22 Jan 2016 10:56:17 GMT

Hi,

I am getting the error "nested aggregation not allowed" for the following Expression:

=sum($(dvQuantity_Pref)*Sum({$<H_ComponentPath={"$(dvItem)*"}>} TotalDemand)) *sum( Only({$<H_ComponentPath={"$(dvItem)*"}>} T$AMNT_SITM))

dvQuantity_Pref = Qtty - 1

dvItem = Item No - xx12542254

(dvItem)* = xx12542254 /1 and xx12542254/2 and............

TotalDemand = 1

T$AMNT_SITM= 4,5

Can you guys please help me to understand the reasons?

Re: Error in expression nested aggregation not allowed

swuehl — Fri, 22 Jan 2016 11:53:54 GMT

You can't just embed a Sum() into another Sum().

You could consider using advanced aggregation (Aggr() function), or maybe just include $(dvQuantity_Pref)

into one of the inner Sum() functions.

To help you more, we need to know the context of this expression.

edit: Same problem with embedding Only() into Sum().

And start with

Dimensions and Measures

It’s all Aggregations

Pitfalls of the Aggr function

Re: Error in expression nested aggregation not allowed

sasikanth — Fri, 22 Jan 2016 12:12:50 GMT

hi,

Try this,

=sum($(dvQuantity_Pref)* (Sum({$<H_ComponentPath={"$(dvItem)*"}>} TotalDemand)) )*

(sum({$<H_ComponentPath={"$(dvItem)*"}>} T$AMNT_SITM))

Re: Error in expression nested aggregation not allowed

Fri, 22 Jan 2016 12:17:59 GMT

thanks...

But the same error message:

Re: Error in expression nested aggregation not allowed

jonathandienst — Fri, 22 Jan 2016 12:35:53 GMT

As the inner aggregation terms return only a single value, so you do not need the two outer sums. Try this expression:

=($(dvQuantity_Pref)) * Sum({$<H_ComponentPath={"$(dvItem)*"}>} TotalDemand) * Only({$<H_ComponentPath={"$(dvItem)*"}>} T$AMNT_SITM)

If that does not work, you may need to use an Aggr() function, but its hard to day without more information about the context iin which this expressions is being used; and your data model.

A sample qvw with some representative data would help, together with a description of what you are trying to achieve, and with expected results.

Re: Error in expression nested aggregation not allowed

Fri, 22 Jan 2016 12:42:47 GMT

This is working :

=Sum({$<H_ComponentPath={"$(dvItem)*"}>} TotalDemand)*sum($(dvQuantity_Pref))*

sum({$<H_ComponentPath={"$(dvItem)*"}>} T$AMNT_SITM)

But the result is incorrect - The Result is over all items

I Need the Result only for

dvItem = Item No ( for example xx12542254 and xx12542254 * .....)

Thank

Re: Error in expression nested aggregation not allowed

Fri, 22 Jan 2016 12:58:39 GMT

Thanks

But the Result ist ' - '

I will create a sample qvw with some representative data.........

Re: Error in expression nested aggregation not allowed

Fri, 22 Jan 2016 20:24:01 GMT

Attached the sample qvw with some data

Re: Error in expression nested aggregation not allowed

swuehl — Fri, 22 Jan 2016 22:19:01 GMT

Maybe like

=Sum(Aggr(($(dvQuantity_Pref)) * Sum({$<H_ComponentPath={"$(dvItem)*"}>} TotalDemand) * only({$<H_ComponentPath={"$(dvItem)*"}>} T$AMNT_SITM),H_ComponentPath))

Re: Error in expression nested aggregation not allowed

Sat, 23 Jan 2016 07:12:37 GMT

Many thx 🙂