Variables/SA

[#1]

Hi,

I'm trying to get a count for the current and previous month using set analysis with a variable:

To get the current month-year:

date(today(),'MMM-YY')

Set analysis formula:

count({$<MonthYear = {$(vcurrmonth)}>}dc_code)

As far as I am aware the MonthYear and current month variable are in the same format.

Can anyone tell me what I am doing wrong?

Thanks.

[swuehl]

Try enclosing your variable in single quotes:

count({$<MonthYear = {'$(vcurrmonth)'}>}dc_code)

[#3]

Thanks for the response.

No luck I'm afraid...although I now get a 0 instead of a null...

[MayilVahanan]

HI

Did u check the format? Its MMM-YY  or MMM-YYYY?

MonthYear format?

After that

count({$<MonthYear = {'$(vcurrmonth)'}>}dc_code)

Hope it helps

[#5]

Hi

Yes I'm sure the format is the same as when I use an IF statement to put a flag on records for the current and previous months that works ok.

Thanks for the reply

[swuehl]

Are you trying to use the set expression in a chart with a date dimension (e.g. YearMonth)? For a comparison of counts for different time periods?

Then try

count(TOTAL {$<MonthYear = {'$(vcurrmonth)'}>}dc_code)

[#7]

Currently I'm not using a dimension - however I tried your suggestion but it still doesn't work.

The variable is an expression, will that make a difference?

[hic]

The format is in principle irrelevant. The comparison is made on the number behind. (It's a dual so the field has both a numeric and a string representation.)

I would define the variable as

Let vCurrMonth = Num(MonthStart(today()));

and use this integer in a comparison with MonthYear (that also should be created using MonthStart).

HIC

[kumarnatarajan]

Check both field format then Try the below syntex

=count({$<MonthYear = {$(=vcurrmonth)}>}dc_code)

[#10]

Thanks Henric

The set analysis doesn't seem to accept using num inside the expression (for the monthyear), is this something that needs to be done in the script?