Expression Query

[dmac1971]

I have a table that has a monthly target in it using Mmm-YYYY for each month, ie Jan-2015, Feb-2015 etc.  I then have a variable that sets the current month, vCurrentMonth, its set using :

=Month(Today()) & '-' & Year(Today())

and gives the value Jan-2015 for today.  I then want this months budget to equal the Jan figure from the table and am using the following expression :

=Sum({<BUD_MONTH_YEAR={$(=($(vCurrMonthYear)))}>} BudgetValue)

But its not working, anyone any ideas?  Thanks.

[sunny_talwar]

Within the Master Calendar are you using the same method to calculate the Month Year? i.e.

MasterCalendar:

Load TempDate as Date,

        Month(TempDate) & '-' & Year(TempDate) as BUD_MONTH_YEAR

Resident YourTable;

If you are using another method to calculate BUD_MONTH_YEAR in the script, then I would use the same method to calculate the vCurrMonthYear variable using the same functions.

Best,

S

[datanibbler]

Hi Dermot,

1) You could use (easier perhaps) the DATE() function to get the year-month

(like >> DATE([date_field], 'MMM-YY') <<)

2) In the set_expression, I think it has to be

=Sum({<BUD_MONTH_YEAR={"$(vCurrMonthYear)"}>} BudgetValue)

Alternatively, I think you could also calculate that variable right there, in the set_expression.

HTH

Best regards,

DataNibbler

[alexandros17]

the correct syntax is:

=Sum({<BUD_MONTH_YEAR={$(vCurrMonthYear)}>} BudgetValue)

let me know

[its_anandrjs]

Hi,

Why not if you use individual filter selection for Year and Month

Sum({<Year ={'$(=Year(Today()))'}, Month={'$(=Month(Today()))'}>} BudgetValue)

Regards

Anand

[dmac1971]

Have found this to work :

=Sum({<BUD_MONTH_YEAR={'$(vCurrMonthYear)'}>} BudgetValue)

Thanks.

[its_anandrjs]

Hi, Dermot,

Another way is to change the variable to pure date format it seems you are using string dates as month year field

Let vCurrMonthYear = MonthName(MakeDate(Year(Today()), Month(Today())))

And in the Expression use

=Sum({<Monthname={"$(vCurrMonthYear)"}>} BudgetValue)

Ex:-

Regards

Anand