Variable in Set Analysis

[darrellbutler]

Hi,

As a newbie can somebody point me in the right direction.

I have created the following variable:

LET

vMaxMonthYr = Month(Today(0)) & '-' &right(year(Today(0)),4);

I then use this in a set expression:

=

sum({$<[MonthYear] = {$('vMaxMonthYr')}, [TYPE] = {"COLL"}, [COMPANY_CODE] = {"0017"} > } [VOL])/1000000

However, the sum calculation doesnt work.

MonthYear is derived from my master calendar and has been calculated using the same formula as the variable.

Any thoughts ?

Kind Regards

Darrell

[Miguel_Angel_Baeyens · Accepted solution]

Hello Darrell,

You were almost there. Take a look at the quoting in the variable:

=sum({$<[MonthYear] = {'$(vMaxMonthYr)'}, [TYPE] = {'COLL'}, [COMPANY_CODE] = {'0017'} > } [VOL])/1000000

Hope that helps.

[darrellbutler]

Excellent - Thankyou Miguel.

Why does the quote start before the dollar ?

Many thanks

[Miguel_Angel_Baeyens]

Hi,

In Set Analysis in particular there are three possible quotings

1.- When the value expected is numeric, no quoting: Sum({< ID = {1000} >} Amount)

2.- When the value is a string (your case), single quoting: Sum({< Name = {'John'} >} Amount)

3.- When the value requires search, double quoting: Sum({< CustomerID = {"AA*"} >} Amount)

In your case, the field returns a literal (string), that it's not quoted within the variable. That's why you need single quoting before and after writing the variable.

Hope that makes sense.