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

[#1]

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

[sunny_talwar]

What is your expression?

[#3]

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

[swuehl]

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.

[#5]

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?

[swuehl · Accepted solution]

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

[#7]

Thanks Stefan, It s working.really nice concept