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

[wynnjimbbt]

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

[sunny_talwar]

Can you share an image of this in a text box object with two dates selected?

=Chr(39) & $(vGetQualfWeekEnding) & chr(39)

[wynnjimbbt]

with two dates selected see below:

possibly we need a closing quote??

[sunny_talwar]

I wonder why there is not closing quote here? Did you def. had Chr(39) at the end?

=Chr(39) & $(vGetQualfWeekEnding) & chr(39)

[wynnjimbbt]

yes; copied it from your post, see below copied back out of text box
also; thanks for helping!

=Chr(39) & $(vGetQualfWeekEnding) & chr(39)

[wynnjimbbt]

Sunny,

possibly we should work to get vGetQualfWeekEnding to return a closing quote?

vGetQualfYear, vGetQualfQuarter, and vGetQualfMonth all work with multiple selections so the basic logic is OK. We are having difficulty evaluating a concatenated string of dates using set analysis.

vGetSelectedCount returns 09/09/2017 with one date selected; 09/09/2017','09/16/2017 with two dates selected and * with no dates selected.

vGetSelectedCount:

=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)),'*'))

[sunny_talwar · Accepted solution]

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

[wynnjimbbt]

in a text box:

=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), ','), '*'))

returns '09/09/2017','09/16/2017'

=$(vGetQualfWeekEnding)

returns Error: Garbage after expression: ','

so; calling the variable fails ...

[sunny_talwar]

=$(vGetQualfWeekEnding)

returns Error: Garbage after expression: ','

so; calling the variable fails ...

Calling this variable where in set analysis?

[wynnjimbbt]

Sunny,
I updated the variable definition in my Dashboard.qvw

evaluated it in a text box

[sunny_talwar]

So it did not work in the text box object? I think it might be easier if you can share a sample or at least some images. I have no idea where are you trying the changes we are mentioning