Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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?
kOUSHIK,
Its nor working...,iam going mad on this issue 😞
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
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
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
Sure Janzen,I did ,
Thanks 🙂
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.
=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.