Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Not applicable

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

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.

Surveys.PNG

Thanks,

Krishna

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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

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

View solution in original post

6 Replies
Highlighted

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

What is your expression?

Highlighted
Not applicable

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

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

Highlighted
MVP
MVP

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

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.

Highlighted
Not applicable

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

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?

Highlighted
MVP
MVP

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

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

View solution in original post

Highlighted
Not applicable

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

Thanks Stefan, It s working.really nice concept