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

Error in expression: Nested aggregation not allowed

alec1982 — Mon, 10 Sep 2012 21:20:42 GMT

Hi I am getting this error when I try to create a table as follow:

LOAD

1 AS #AnnualCostCounter,

%LeaseKey,

Sum(If ((ExpenseFrequency ='Annually'),Max([Expense Amount (USD)]),

If ((ExpenseFrequency ='English Quarters'),(Max([Expense Amount (USD)])*4 ),

If ((ExpenseFrequency ='New Scottish Quarters'),(Max([Expense Amount (USD)]*4 )),

If ((ExpenseFrequency ='One-time'),(0),

If ((ExpenseFrequency ='Quarterly'),(Max([Expense Amount (USD)])*4 ),

If ((ExpenseFrequency ='Scottish Quarters'),(Max([Expense Amount (USD)])*4 ),

If ((ExpenseFrequency ='Semi-Annually'),(Max([Expense Amount (USD)])*2 ),

0))))))) ) as [Annualized Cost]

RESIDENT

Expense

where [Expense Type]='Base Rent'

Group by %LeaseKey

Anybody might be able to help.

Thxs in advance,

Re: Error in expression: Nested aggregation not allowed

Anonymous — Tue, 11 Sep 2012 00:07:10 GMT

Can't help - for the same reason that QV can't calculate this - but can expalin.

You have aggregation function Max() within another aggregation function Sum(). It doesn't have meaning. In chart expressions, you can do something like this using aggr() function:

sum(aggr(max(amount), <some fields by wghich you aggregate max>))

In script, you use "group by" to tell by what fields to aggregate. In your case, you calculate sum() by %LeaseKey. It has nothing to do with max(). From the description, I can't guess what that max() means - max by what? Probably (just guessing), you need to find out your max-es first, create them as new fields, and use these newe fields in your sum(). I cannot guess by what the max-es should be aggregated in your case.

Maybe you don't need max() at all(?)

Hope it will give you an idea what to try.

Regards,

Michael

Re: Error in expression: Nested aggregation not allowed

alec1982 — Tue, 11 Sep 2012 01:39:32 GMT

Hey Mike,

Thank you for your explenation.

What I am trying to do is as follow

I have a table:

%LeaseKey Expense Frequency Expense Amount

1 Monthly 1000

1 Monthly 500

1 Monthly 750

1 Monthly 1000

2 Yearly 500

2 Yearly 625

3 Semi Annual 400

3 Semi Annual 250

I am trying to create a table to give me

%LeaseKey Max(Expense Amount) Annual Amount

1 1000 1000*12

2 625 625*1

3 400 400*2

Let me know if you might be able to help.

Thank you much.

Alec,

Re: Error in expression: Nested aggregation not allowed

Anonymous — Tue, 11 Sep 2012 10:31:19 GMT

This works.

(Apparently there should be a more elegant ways.)

tmp:
LOAD
%LeaseKey,
max([Expense Amount]) as MaxExpense
RESIDENT Expense
GROUP BY %LeaseKey;
LEFT JOIN (tmp) LOAD
%LeaseKey,
[Expense Frequency]
RESIDENT Expense;

Result:
LOAD
%LeaseKey,
MaxExpense,
MaxExpense * if([Expense Frequency]='Monthly', 12, if([Expense Frequency]='Yearly', 1, 2)) as [Annual Amount]
RESIDENT tmp;

DROP TABLES Expense, tmp;

Re: Error in expression: Nested aggregation not allowed

alec1982 — Tue, 11 Sep 2012 16:22:20 GMT

Hey Mike,

This helped a little bit but I am still getting an issue.

When I create the table in the script and then add the table into table box it shows the data correctly. when I add the same fields in a table chart and do sum([Annual Amount]) it returns a random nr which is away higher than what it should be.

I am assuing that i should get the same nr when using Sum([Annual Amount]) as [Annual Amount] because the lease key is uniqe on this table.

Please let me know your thoughts and thank you so much for your help.

Best,

Alec