problem with set analysis with string variable

[Anonymous]

Good afternoon,

In the enclosed sample I tried without success to show the collected cash of a game for the same month but a year back of the year that I have currently selected.

my script is

LOAD datecollect,

          game,

          Money([cash],'€ #.##0') as cash,

          MonthName(datecollect) as MonthYear,

          Month(datecollect) as Month,

          Year(datecollect) as Year

FROM

(ooxml, embedded labels);

//set vmyMonthpreviousYear = "(Month & ' ' & (Year-1))";

set vmyMonthpreviousYear = MonthName(AddMonths(datecollect, -12))

I can correctly show in a text box the value of the variable $(vmyMonthpreviousYear) which is the same month as the selected one and but one year back of the year that I have currently selected.

I can't find the correct set analysis in order to show the cash using the variable, while I can show the cash of a year using

=Sum({$<Year={'2016'},Month=>}cash)

maybe I am trying to use the wrong variable or I have taken it from the wrong way ? or it is a matter of syntax?

I thought that

=Sum({$<MonthYear={'$(vmyMonthpreviousYear)'}>}cash)

would work but it does not...

Your help is appreciated

Regards

Antonio

[sunny_talwar · Accepted solution]

Try this:

=Sum({$<MonthYear={'$(=$(vmyMonthpreviousYear))'}, Year, Month>}cash)

[Anonymous]

Hi Sunny,

Thanks!

So, in the set analysis the dimension MonthYear, created during the script load, needs the dimension Year when using the variable:

=Sum({$<MonthYear={'$(=$(vmyMonthpreviousYear))'},Year>}cash)

Regards

Antonio

P.S.: I checked and just using Year does the job, do not need Month.

        In my live envirenment I had to remove the dimension MonthYear from the straight table.