Complex variable in set expression

[piotrtk]

Hi,

Currently, I use following expresion in set analysis:

Sum({<Date_Select={'$(vDate_Sel1)', '$(vDate_Sel2)', '$(vDate_Sel3)', '$(vDate_Sel4)' }>} Sales_Amount

and each variable has a date assigned, like '03/10/2018', '10/03/2018', .....

When I try to use one variable 'vDate_Sel' with value like '03/10/2018, 10/03/2018, ...' then set analysis doesn't work. Probably, it is being treated as a string, not separated date values.

How should I use 'vDate'Sel' variable with multiple date values to work correctly in set expression?

Regards,

Piotr

[sunny_talwar · Accepted solution]

You need to try vDateSel like this

'03/10/2018', '10/03/2018', ...'

[MK_QSL]

What is the format of your date field Date_Select?

[piotrtk]

Sorry, it doesn't work.

[piotrtk]

This is a little more complex as I have described. Actually, each variable includes 2 dates separated by '|' in format YYYY-MM-DD, like

2018-03-10|2017-12-31

But anyway, I can't create one variable with few such values.

[sunny_talwar]

ok

[piotrtk]

At the end I have found it works:

"You need to try vDateSel like this

'03/10/2018', '10/03/2018', ...'"

But in set expression I need to write:

Date_Select={$(vDate_Sel)}

instead of: Date_Select={'$(vDate_Sel)'}

Not sure why, but it works in this way.

Thank you!

[cheenu_janakira]

Two other possibilities:

I don't know the underlying structure of the VARs "Dat_Sel" but have you tried considered "CHR(39) & Concat(DISTINCT [Date Field], CHR(39) & ',' & CHR(39)) & CHR(39)" to create your full string of dates with single-quote separation.

Alterniatively, have you considered double-barrelling columns in said table one where date is a NUM (for set analysis) and one (as is present) where Date is in a user-friendly format.

Then in set analysis you put: SUM({$<Date_NUM = {$(=[Expression to return all dates as NUM separated by comma]), Date_Select=}>} Value).

The "Date Select=" in the set analysis will allow user to make selections on this field and the "Date_NUM [with expression]" will resolve for the right dates.

Worth a try maybe.