MTD not working

[#1]

Hi all,

Iam wroung some where please do let me know

I am just calculation YTD and MTD using set analysis

here is my expression

=if(Category='receivable',sum({<YEAR={'<=$(=MAX(YEAR))'},MONTH={'<=$(=MAX(MONTH))'}>} $(EXP)))

here if YTD is working fine,but not MTD ..,

please do let me know where iam wroung

Regards,

Kiruthiga

[#2]

Hi,

What is your common field between tables? Is it by date or month and year only? Sometimes the expression varies. Please try this expression.

=if(Category='receivable',sum({$<Year = {"<=$(=max(Year))"},Month = {"<=$(=max(Month))"},Month =,Year = >} $(EXP)))

Hope to hear from you,

Regards,

Janzen

[#3]

You can use below script for ytd

Sum({<Year={$(=Max(Year))},AMonth={$(=Max({<Year={$(=Max(Year))}>}AMonth))},ADate={"<=$(Date(Max(ADate)))"}>} Qty)

It will help you bit.

[#4]

Janzen,

its not woking,Actually if i try with my expression just in my above post,when i replace the year field to month ,

its not working,

means if i select jan alone with out selecting any year,its displaying nothing..,

but it need to give jan month data field right?

[#5]

kOUSHIK,

Its nor working...,iam going mad on this issue 😞

[#6]

Hi,

You dont need to replace the year field to month. For MTD, my last expression should work. Based on your example, you should also choose a year after choosing january as a month to determine the end date. So this means from January 2010 upto January of the selected year.

Regards,

Janzen

[#7]

Janzen,

Thanks..,

yup its working with the concept of January 2010 uptoJan selected year

sorry confused...a little,

if i select march 2012,

can i get jan 2010 to mar 2012 ?(sum of values from jan 2000 to mar 2012 as an expression value,means a total of last selected month and year from the starting year and month irrespective of month alone)

really thanks for your valuable timingssssssss

Regards,

-Keeruthii

[#8 · Accepted solution]

Hi,

Yes as long as you have your data up to March 2012. If it gives you the right answer, kindly tag it as the correct answer for future references.

Regards,

Janzen

[#9]

Sure Janzen,I did ,

Thanks 🙂

[#10]

Try below code.

sum({<Year={'$(vMaxYear)'},Month={'$(vMaxMonth)'},Day={'<=$(vMaxDay)'},Date=,MonthYear=>} Amount)

Variables:-

vMaxYear=Max(Year)

vMaxMonth=Date(max(Date),'MMM')

vMaxDay=day(max(Date))

Don't worry.I will help you to become MAD..

Just Kidding.

Try it.

[#11]

=Sum({$<Date = {">=$(vMinnDate)<=$(vMaxDate)"}>} QTY)

In ur calendar use below code..

Caltemp:

LOAD
num(min(%TranDate)) as MinnDate

resident TRANSACTION_TABLE;

LET vMinnDate = peek('MinnDate',0,'Caltemp');

Declare a variable...

vMaxDate  = date(MonthEnd(max(Date)),'DD MMM YYYY')

It will be helpful.