Error in expression: Nested aggregation not allowed

[alec1982]

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,

[Anonymous]

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

[alec1982]

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,

[Anonymous · Accepted solution]

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;

[alec1982]

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