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

[jonnaamb]

Hello all,

I'm using an expression in QlikView for a series of values from a field. In this period of the year, currently we have the below days.

'20180812','20180813','20180814','20180815','20180816','20180817','20180818','20180820','20180821','20180822','20180823','20180824','20180827','20180828','20180829','20180830','20180831','20180903','20180904','20180905','20180906','20180907'

Out of which, I need days until yesterday, (assume today is 20180820)

'20180812','20180813','20180814','20180815','20180816','20180817','20180818'

So I used the below script, and it works as expected, by using a variable - $(vCurPeriodDatesSep_UntilYesterday)

Chr(39)

&

LEFT(REPLACE(

CONCAT({<PeriodInd = {$(vCurPeriodIndStatic)},

[Calendar Date] = P([Calendar Date])>}distinct [Calendar Date], '*,*')

,

'*',

Chr(39))

,

INDEX(REPLACE(

CONCAT({<PeriodInd = {$(vCurPeriodIndStatic)},

[Calendar Date] = P([Calendar Date])>}distinct [Calendar Date], '*,*')

,

'*',

Chr(39)), Date(Today(), 'YYYYMMDD'))-3)

But when I'm using the same in a table within Set Analysis like shown below, it shows blank and mentions the error - Error in set modifier ad hoc element list: ',' or ')' expected

=Sum({1<[Business Segment] = {'*'},

   [Calendar Date] = {$(vCurPeriodDatesSep_UntilYesterday)},

   PeriodInd = {$(vCurPeriodIndStatic)},

   Country = {'$(vCountrySelected)'},

   [KeyFigure] = {'$(vCurSAKeyFigure)'}>}[Value])

Please help me with this issue dear experts.

Thanks and regards,
Ambareesh Jonnavittula

[sunny_talwar]

Would you be able to share a sample where we can see the issue?

[jonnaamb]

Hello Sunny, thank you for your quick response. Please see the attachment scenario.

[sunny_talwar · Accepted solution]

Try this

=Sum({1<[Calendar Date] = {$(=$(vCurPeriodDatesSep_UntilYesterday))}>}[Value])

[Frank_Hartmann]

Add an equal sign to your variable declaration

=Chr(39)

&

LEFT(REPLACE(

CONCAT({<

[Calendar Date] = P([Calendar Date])>}distinct [Calendar Date], '*,*')

,

'*',

Chr(39))

,

INDEX(REPLACE(

CONCAT({<

[Calendar Date] = P([Calendar Date])>}distinct [Calendar Date], '*,*')

,

'*',

Chr(39)), Date(Today(), 'YYYYMMDD'))-3)

[jonnaamb]

Thank you both. The issue is resolved.

[vishsaggi]

Do you always calculate the expression until yesterday then may be you can also use like

=Sum({<

   [Calendar Date] = {"<$(=Date(Today(), 'YYYYMMDD'))" }>}[Value])