MTD error

[sifatnabil]

Hi all,

I am trying to implement Month to date with the following formula:

but it doesn't work. Do I have the define the (=max(Year)) and (=max(Month)) variables somewhere?
In my load script the dates are defined like this:


day(DATE_1) as DayNumber,

WeekDay(DATE_1) as Day,

month(DATE_1) as Month,

Year(DATE_1) as Year,

date(WeekEnd(DATE_1,0,-2),'D/M/YY') as Week,

[#2]

sorry, I don't understand why you need the DATE_1 condition here. I believe you're talking about MTD.

[sifatnabil]

Yes I'm referring to MTD. DATE_1 is the field name of the date. Should it be removed from the set analysis expression completely? Like this? The value returns 0.

sum({<Year={$(=Max(Year))},Month={$(=max(Month))}>} totalbrokerage)

[#4]

You are missing a double quote in the month condition. PFA. You don't need the date condition.

[sifatnabil]

This works, but once I select March 12th on the Calendar, it just shows the sum for March 12.

Is there a way that I can keep a static MTD, that doesn't change if I select a day?

[#6 · Accepted solution]

PFA.

Thanks,

Ram

[sifatnabil]

Brilliant, thanks!!