topic Re: nested aggregation not allowed in QlikView

nested aggregation not allowed

Sat, 31 May 2014 14:46:49 GMT

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?

Re: nested aggregation not allowed

swuehl — Sat, 31 May 2014 14:59:16 GMT

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.

Re: nested aggregation not allowed

Sat, 31 May 2014 16:57:27 GMT

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?

Re: nested aggregation not allowed

whiteline — Sat, 31 May 2014 17:36:31 GMT

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.

Re: nested aggregation not allowed

Anonymous — Sat, 31 May 2014 20:30:27 GMT

Hello,

Please, try and see if this works for you:

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

Re: nested aggregation not allowed

Sat, 31 May 2014 21:35:29 GMT

Guys,

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

Thanks everyone!

Re: nested aggregation not allowed

preminqlik — Sun, 01 Jun 2014 07:25:49 GMT

try this

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

Re: nested aggregation not allowed

hic — Sun, 01 Jun 2014 19:03:48 GMT

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

Re: nested aggregation not allowed

Mon, 02 Jun 2014 16:41:23 GMT

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

Re: nested aggregation not allowed

swuehl — Mon, 02 Jun 2014 18:09:41 GMT

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

Re: nested aggregation not allowed

Mon, 02 Jun 2014 18:30:15 GMT

I do not have access to the scripts.