topic MTD error in QlikView

MTD error

sifatnabil — Thu, 03 Apr 2014 01:31:40 GMT

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,

Re: MTD error

Thu, 03 Apr 2014 02:12:41 GMT

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

Re: MTD error

sifatnabil — Thu, 03 Apr 2014 02:16:58 GMT

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)

Re: Re: MTD error

Thu, 03 Apr 2014 02:24:33 GMT

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

Re: MTD error

sifatnabil — Thu, 03 Apr 2014 02:33:58 GMT

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?

Re: Re: MTD error

Thu, 03 Apr 2014 02:36:32 GMT

PFA.

Thanks,

Ram

Re: Re: MTD error

sifatnabil — Thu, 03 Apr 2014 02:46:47 GMT

Brilliant, thanks!!