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

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

wynnjimbbt — Wed, 25 Nov 2020 16:16:04 GMT

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

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

sunny_talwar — Fri, 05 Jan 2018 01:28:45 GMT

How is this defined?

vGetQualfWeekEnding

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

Anil_Babu_Samineni — Fri, 05 Jan 2018 03:51:51 GMT

Perhaps this?

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

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

pradosh_thakur — Fri, 05 Jan 2018 05:48:35 GMT

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

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

sunny_talwar — Fri, 05 Jan 2018 09:12:30 GMT

How did you know what vGetQualfWeekEnding is?

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

pradosh_thakur — Fri, 05 Jan 2018 09:15:49 GMT

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

sunny_talwar — Fri, 05 Jan 2018 09:17:38 GMT

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

Thanks for sharing this

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

wynnjimbbt — Fri, 05 Jan 2018 14:30:56 GMT

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

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

sunny_talwar — Fri, 05 Jan 2018 14:43:07 GMT

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

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

wynnjimbbt — Fri, 05 Jan 2018 14:48:10 GMT

without the single quotes this returns the error message:

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

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

sunny_talwar — Fri, 05 Jan 2018 14:50:26 GMT

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

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

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

wynnjimbbt — Fri, 05 Jan 2018 14:53:55 GMT

with two dates selected see below:

possibly we need a closing quote??

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

sunny_talwar — Fri, 05 Jan 2018 14:56:20 GMT

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

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

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

wynnjimbbt — Fri, 05 Jan 2018 14:59:55 GMT

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

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

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

wynnjimbbt — Fri, 05 Jan 2018 15:29:37 GMT

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:

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

sunny_talwar — Fri, 05 Jan 2018 15:39:10 GMT

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

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

wynnjimbbt — Fri, 05 Jan 2018 15:53:04 GMT

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 ...

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

sunny_talwar — Fri, 05 Jan 2018 15:56:05 GMT

=$(vGetQualfWeekEnding)

returns Error: Garbage after expression: ','

so; calling the variable fails ...

Calling this variable where in set analysis?

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

wynnjimbbt — Fri, 05 Jan 2018 16:01:00 GMT

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

evaluated it in a text box

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

sunny_talwar — Fri, 05 Jan 2018 16:10:31 GMT

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