topic How to Overcome the issue'Error: Error in set modifier ad hoc element list: ',' or ')' expected' in pivot table in QlikView

How to Overcome the issue'Error: Error in set modifier ad hoc element list: ',' or ')' expected' in pivot table

Thu, 30 Mar 2017 19:15:44 GMT

Hi All,

I created a pivot table having 34 million records. i am filtering the data by year wise.I am not able to see the data for all the years.

By year wise i can see data. if i select more than 1 year ,am getting the below error.How to eliminate the below issue.

Thanks,

Krishna

Re: How to Overcome the issue'Error: Error in set modifier ad hoc element list: ',' or ')' expected' in pivot table

sunny_talwar — Thu, 30 Mar 2017 19:16:31 GMT

What is your expression?

Re: How to Overcome the issue'Error: Error in set modifier ad hoc element list: ',' or ')' expected' in pivot table

Thu, 30 Mar 2017 19:24:39 GMT

Hi Sunny,

I created few dimensions in the Script and used these dimensions in the pivot table for KPI calculation.I have 9 KPI's in my chart.

Ex1:

The below code was used in scripting to Create the dimensions of NursesUnderstandingandCaringNum and

NursesUnderstandingandCaringDem.

My Logic for KPI is

count(NursesUnderstandingandCaringNum)/count(NursesUnderstandingandCaringDem)

NursesUnderstandingandCaringNUM:

load

if(ShortText='Nurses Understanding and Caring',SurvID) as NursesUnderstandingandCaringNum,

SurvID

resident SurveyDataWithAll

where Answer='1' and not isnull(SurvID) and not isnull (if(ShortText='Nurses Understanding and Caring',SurvID));

NursesUnderstandingandCaringDEM:

load

if(ShortText='Nurses Understanding and Caring',SurvID) as NursesUnderstandingandCaringDem,

SurvID

resident SurveyDataWithAll

where (Answer=0 or Answer=1 or Answer=2 or Answer=3 or Answer=4 or Answer=5 or Answer=6 or Answer=7 or Answer=8 or Answer=9 or Answer=10)

and not isnull(SurvID) and not isnull (if(ShortText='Nurses Understanding and Caring',SurvID));

Re: How to Overcome the issue'Error: Error in set modifier ad hoc element list: ',' or ')' expected' in pivot table

swuehl — Thu, 30 Mar 2017 19:42:07 GMT

The error message is indicating a problem with a set expression in one of the aggregations used in the chart.

I assume you are using a dollar sign expansion in one of the set modifiers that does not proper expand to a value list when you select more than one year.

Re: How to Overcome the issue'Error: Error in set modifier ad hoc element list: ',' or ')' expected' in pivot table

Thu, 30 Mar 2017 19:49:53 GMT

Hi Stefan, I have data from Year 2011 to 2017.

i am getting the issue only for the years having large data.Whenever selecting the 2016 year ,i can see the error.

I am using variables in my chart, like $(vCleanliScores). This dollar sign is effecting my chart?

Re: How to Overcome the issue'Error: Error in set modifier ad hoc element list: ',' or ')' expected' in pivot table

swuehl — Thu, 30 Mar 2017 19:53:42 GMT

This will expand the variable to text, this text will then be evaluated as part of your expression.

To understand what is causing your issue, you would need to trace all dollar sign expansions in the context of selection state and examine the final expression.

The Magic of Dollar Expansions

Re: How to Overcome the issue'Error: Error in set modifier ad hoc element list: ',' or ')' expected' in pivot table

Fri, 31 Mar 2017 15:33:41 GMT

Thanks Stefan, It s working.really nice concept