Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try enclosing your variable in single quotes:
count({$<MonthYear = {'$(vcurrmonth)'}>}dc_code)
Thanks for the response.
No luck I'm afraid...although I now get a 0 instead of a null...
HI
Did u check the format? Its MMM-YY or MMM-YYYY?
MonthYear format?
After that
count({$<MonthYear = {'$(vcurrmonth)'}>}dc_code)
Hope it helps
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
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)
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?
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
Check both field format then Try the below syntex
=count({$<MonthYear = {$(=vcurrmonth)}>}dc_code)
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?