nested aggregation not allowed

[#1]

Hi,

I am getting the error "nested aggregation not allowed" for the following expression...

sum(if (FirstSortedValue(Date(TxnDate), TxnDate) = '1/2/2012', Amount, 0))

Can you guys please help me understand the reasons?

[swuehl]

You can't embed an aggregation function like FirstSortedValue() inside another, like Sum().

aggregation functions need a set of records to operate on, while an aggregation function itself returns a scalar.

You can look into advanced aggregation function (aggr() function) to get around the limitations, but it might be better if you explain what you want to achieve, maybe there is another, better  option.

[#3]

Well I had high hope and expectations from QlikView until I saw this question.
I have been using OBIEE and it's so damn restricted and limited in terms of "this exact aspect". NO SUPPORT FOR NESTED AGGREGATED QUERIES.

What use can it be if we are unable to apply aggregations on top of an aggregation?
Is this feature available in Qlik or not? If how any of you guys have achieved nested aggregation workaround in Qlik?

[whiteline]

You don't bother your self reading the manuals or so, do you ?

If you do, you'd notice that there is an article 'Nested aggregations' in QV help.

[Anonymous]

Hello,

Please, try and see if this works for you:

if (Min(TxnDate) = Num(Date('1/2/2012')), Sum(Amount))

[#6]

Guys,

I got it working at saving my FirstSortedValue expression in a variable.

Thanks everyone!

[preminqlik]

try this

sum(if(aggr(FirstSortedValue(Date(TxnDate), TxnDate),dimension)= '1/2/2012',Amount,0))

[hic]

Just to make it completely clear: You can have as many levels of nested aggregations as you want! Example:

     Sum(...)

     Sum(Aggr(Sum(...),...))

     Sum(Aggr(Sum(Aggr(Sum(...),...)),...))

     Sum(Aggr(Sum(Aggr(Sum(Aggr(Sum(...),...)),...)),...))

are all valid structures. ("Sum" can be exchanged with any aggregation function, and scalar functions can be inserted anywhere.) Read more here.

But every level means a hit in the performance, so think before you do it...

HIC

[#9]

Guys,

here is is my real expression which I want to build...

=Sum(Aggr(If(MonthName(Min({1<TxnType={'Invoice'}>}TxnDate)) = MonthName(Max(TxnDate)),

sum({<TxnDate={'$(=$(Date1))'}, AccountType={'Income'}>} AmountWithSign),0),CustomerListID))

The Date1 is defined as

FirstSortedValue(Date(TxnDate), Aggr(Min({<TxnType={'Invoice'}>TxnDate), CustomerListID, TxnDate))

The purpose of this expression is calculate the sum of first invoice of those customer who have their first ever invoices fallen in a particular month.

Here is the sample data

Customer      First Invoice     Invoice in May 2103      Amount

1.     Cust1           3/25/2103         3/25/2013                     $25

2.     Cust1           3/25/2013          3/27/2013                    $125

3.     Cust2            3/15/2013          3/15/2103                    $100

4.     Cust2            3/15/2013          3/18/2013                    $120

5.     Cust4              1/2/2013           3/28/2013                    $100

So the output I need is sum of amount of 1 and 3 i.e. 25  + 100 = $125.

With the expression given above I am able get the amount of minimum date of one customer. in this exmaple i will only $25.

Can you guys help me fix it?

Regards,

Saurabh

[swuehl]

Could you create a flag in the script for the records with each customers first invoice?

Then just create a set expression selecting on this flag (and maybe the month of interest or just use the month as dimension).