Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
wynnjimbbt
Contributor III
Contributor III

Error in set modifier ad hoc element list: ',' or ')' expected ; GetSelection variables

have trouble evaluating vGetQualfWeekEnding when 2 or more dates are selected:

all other GetSelection variables work, and vGetQualfWeekEnding works with 1 date selected.

ex.

=$(vGetQualfWeekEnding) returns 09/23/2017, 09/30/2017

=sum({$<  LogonID ={"*"}, [Week Ending] = {'$(=$(vGetQualfWeekEnding))'} >}[Target PMP Ref] )  returns a zero; there is data

=$(ePMPRefTarget) returns " Error in set modifier ad hoc element list: ',' or ')' expected "

ePMPRefTarget:

LogonID ={"*"},[Ref Qualified Date]=, [Ref Qualified Year]=, [Ref Qualified Quarter]=, [Ref Qualified Month]=, [Ref Qualified Week Ending]=, Advisor=, [Group]=, [Region Team]=,Year = {$(=max(Year))} , Quarter = { $(=$(vGetQualfQuarter))} , Month = { $(=$(vGetQualfMonth))}, [Week Ending] = {'$(=$(vGetQualfWeekEnding))'}

Thanks in advance!

Jim

1 Solution

Accepted Solutions
sunny_talwar

Try this as your variable


=If(GetSelectedCount([Ref Qualified Week Ending])>0,

Concat(DISTINCT Chr(39) & Date([Ref Qualified Week Ending], 'MM/DD/YYYY') & Chr(39), ','),

If(GetSelectedCount([Week Ending])>0,

Concat(DISTINCT Chr(39) & Date([Week Ending],'MM/DD/YYYY') & Chr(39), ','), '*'))

and then this

=Sum({$< LogonID ={"*"}, [Week Ending] = {$(vGetQualfWeekEnding)}>}[Target PMP Ref])

or this

=Sum({$< LogonID ={"*"}, [Week Ending] = {$(=$(vGetQualfWeekEnding))}>}[Target PMP Ref] )

View solution in original post

25 Replies
sunny_talwar

How is this defined?

vGetQualfWeekEnding

Anil_Babu_Samineni

Perhaps this?

=sum({$<LogonID ={"*"}, [Week Ending] = {$(=Concat( Chr(39) & $(vGetQualfWeekEnding) & Chr(39), ','))} >}[Target PMP Ref] )

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
pradosh_thakur
Master II
Master II

=sum({$<  LogonID ={"*"}, [Week Ending] = {'$(=chr(39) & $(vGetQualfWeekEnding) & chr(39))'} >}[Target PMP Ref] )



vGetQualfWeekEnding

=if(GetSelectedCount([Ref Qualified Week Ending])>0,
CONCAT(Distinct Date([Ref Qualified Week Ending],'MM/DD/YYYY'),chr(39)& ',' & chr(39)),
if(GetSelectedCount([Week Ending])>0, CONCAT(Distinct Date([Week Ending],'MM/DD/YYYY'),chr(39)& ',' & chr(39)),'*'))


Learning never stops.
sunny_talwar

How did you know what vGetQualfWeekEnding is?

pradosh_thakur
Master II
Master II

Learning never stops.
sunny_talwar

Hahahahaha make sense... I was wondering where did you get that information from

Thanks for sharing this

wynnjimbbt
Contributor III
Contributor III
Author

All,

vGetQualfWeekEnding:   this works;
returns 09/09/2017 with one date selected; 09/09/2017','09/16/2017 with two dates selected and * with no dates selected

=if(GetSelectedCount([Ref Qualified Week Ending])>0,
CONCAT(Distinct Date([Ref Qualified Week Ending],'MM/DD/YYYY'),chr(39)& ',' & chr(39)),
if(GetSelectedCount([Week Ending])>0, CONCAT(Distinct Date([Week Ending],'MM/DD/YYYY'),chr(39)& ',' & chr(39)),'*'))

evaluating vGetQualfWeekEnding below:   this fails;
returns Error in set modifier ad hoc element list: ',' or ')' expected

=sum({$< LogonID ={"*"}, [Week Ending] = {'$(=chr(39) & $(vGetQualfWeekEnding) & chr(39))'} >}[Target PMP Ref] )

Jim

sunny_talwar

Can you try this without the red single quotes

=Sum({$< LogonID ={"*"}, [Week Ending] = {'$(=chr(39) & $(vGetQualfWeekEnding) & chr(39))'} >}[Target PMP Ref])

So, essentially try this

=Sum({$< LogonID ={"*"}, [Week Ending] = {$(=Chr(39) & $(vGetQualfWeekEnding) & chr(39))}>}[Target PMP Ref] )

wynnjimbbt
Contributor III
Contributor III
Author

without the single quotes this returns the error message:

Error in set modifier ad hoc element list: ',' or ')' expected