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

Error:Error in expression:Nested aggregation not allowed

prasadcm — Mon, 11 Sep 2017 09:06:57 GMT

Hello All,

I have an expression like below in textbox

=sum({<Distribution={1},Node_TS_Year=,Node_TS_Month=,Node_TS_Date=,[Task Category]={"Cargotec ERP","Global versions"},Node_TS_Date={">=$(=Date(Today()-30))<$(=Date(Today()))"}, [Task Status]={'Completed', 'Warning'}>}

(if([Task Name]='AP Explorer_SAN',30,

if([Task Name]='FMG Explorer_SAN',30,

if([Task Name]='Reload of Quotation Hit Rate',30,

if([Task Name]='Reload of Sales Variance',30,

if([Task Name]='Reload of Service lead time',30,

if([Task Name]='Reference List',30,

if([Task Name]='SO Condition Type',vTotalMondays,

if([Task Name]='Inventory report_SAN',30,

if([Task Name]='Reload of GL line items',30,

if([Task Name]='Customs reporting Malaysia',30,

if([Task Name]='Reload of Freight report',30,

if([Task Name]='Macgregor Migrated Project Life Explorer',30,

if([Task Name]='Parts - Sales Dashboard_SAN',30,

if([Task Name]='Production Order',30,

if([Task Name]='Material Delivery Status',30,

if([Task Name]='Production Order Dashboard',30,

if([Task Name]='Reload Equipment Lead Time',30,

if([Task Name]='Customer Statistics Finland',30,

if(Match([Task Name],'people Explorer','Closing Explorer','GM and OP analysis light'),Count({<Distribution={1},[Task Category]={$(=vTaskCategory)},[Task Name]-={$(=vTaskNameEx)},Node_TS_Date={">=$(=Date(Today()-30))<$(=Date(Today()))"}, [Task Status]={'Completed', 'Warning','Failed'}>}Node_TS_Hour)

,vReloadMonthlyCount)))))))))))))))))))))

I get error as Error:Error in expression:Nested aggregation not allowed

Regards,

Prasad Mayekar

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

Peter_Cammaert — Mon, 11 Sep 2017 10:45:10 GMT

You cannot embed one aggregation function inside a call to another aggregation function, except if you use aggr().

Simplified, your expression looks like this:

=Sum({Set spec} if (a, 30,... if (Match(), Count({Set spec} Node_TS_Hour), vReloadMonthlyCount ))...))

meaning that you try to sum the output of a Count(). Which is a nested aggregation and is not allowed without the use of aggr().

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

techvarun — Mon, 11 Sep 2017 10:48:50 GMT

Hi Prasad,

share a sample to work

thanks

Varun

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

Kushal_Chawda — Mon, 11 Sep 2017 11:35:39 GMT

You need to use the Aggr to use nested aggregation functions.

(if(Your Full if condition), Dimension1,Diemnsion2) )

where dimension1 & dimension2 are the dimensions of the chart

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

prasadcm — Wed, 13 Sep 2017 06:17:18 GMT

Hi Kushal,

It worked,thanks

now the issue is I have dimension has 'vTaskName'

where vTaskName=

if(Distribution='1',(if(match([Task Category],'Global versions','Cargotec ERP'),

if(not WildMatch([Task Name],'*Distribution*','*Tiina*''*Purchase Orders new Report*','*new Report*','*one table*'),

[Task Name]))))

i tried with vTaskName in Dimension1 but it didn't work.

Regards,

Prasad Mayekar

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

Kushal_Chawda — Wed, 13 Sep 2017 06:39:57 GMT

use [Task Name] instead of vTaskName

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

prasadcm — Wed, 13 Sep 2017 06:47:34 GMT

I had used dimension has [Task Name] before,but i get values for all the tasks

whereas i dont need all the tasks,i need to exclude few of them,which i have mentioned in previous comment.

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

Kushal_Chawda — Wed, 13 Sep 2017 06:52:41 GMT

If you have checked suppress NULL in your dimension, it should not be the problem.