Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Usually YTD calculations will pick the range from January to current date. But how can I modify it to start from April instead? e.g. if it's 1st June 2013 today, YTD will be sum from April 1st 2013 to 1st June 2013.
I know the MTD in set analysis is this:
sum({<date=,Year={$(=Max(Year))},Month={"=$(=max(Month))"}>} sales)
Could I get the YTD format and also make it start from April? Thanks a lot.
Hi Sifat, Attaching a simple example. Hope you find it useful.
Thanks,
Ram
Try this,
sum({<date=,Year={$(=Max(Year))},Month={">=$(=MinMonth)"}>} sales)
Suraj
Thanks Suraj - what is MinMonth? Do I need to define it somewhere?
You should add to the load script two statements which define your new year and than you will be able to operate them as usual.
year(yearstart(Date(Date),1,3)) as [Fiscal Year],
if(num(Month(Date(Date)))<4, num(Month(Date(Date)))+9,num(Month(Date(Date)))-3) as [Fiscal Period]
Yepp. It is a variable and you can define whatever you want.
Suraj
Try this
Your calender should be financial calender like this
Year(AddMonths(DateField,9)) AS Year
Month(DateField) AS Month
Num(Month(AddMonths(DateField,9))) AS MonthNum
Create variables
vMaxYear : = Max(Year)
vMaxMonth : = IF(Year=$(vMaxYear),Max(MonthNum))
This is your expression:
=sum({<Year={$(vMaxYear)},MonthNum={"<=$(vMaxMonthNum)"}>} sales)
I think you should explore the InYeartoDate function. It has an option to say when the financial year starts.
if inyeartodate(date,yearstart(today()),0,4), <your expression>.
Hi Sifat, Attaching a simple example. Hope you find it useful.
Thanks,
Ram
Hi,
Try this expression
=sum({<Month=, Year=, date={'>=$(=YearStart(Today(), 0, 4))<=$(=Today())'}>} sales)
If it not works then check the date format of date and Today() values, if it is not same then format the date.
Regards,
Jagan.
Thanks, this worked!