Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

nested aggregation not allowed

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?

Tags (2)
10 Replies
Highlighted
MVP
MVP

Re: nested aggregation not allowed

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.

Highlighted
Not applicable

Re: nested aggregation not allowed

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?

Highlighted
Master II
Master II

Re: nested aggregation not allowed

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.

Highlighted
Creator III
Creator III

Re: nested aggregation not allowed

Hello,

Please, try and see if this works for you:

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

Highlighted
Not applicable

Re: nested aggregation not allowed

Guys,

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

Thanks everyone!

Highlighted
Specialist II
Specialist II

Re: nested aggregation not allowed

try this

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

Highlighted

Re: nested aggregation not allowed

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

Highlighted
Not applicable

Re: nested aggregation not allowed

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


Highlighted
MVP
MVP

Re: nested aggregation not allowed

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