Year to Date In Fiscal Year

[sukydhak]

Guys

I have two issues. I need to create a year to date chart and normally I would use the below statements but I can't get these working with Fiscal years

!TEXT=Year To date This Year

=sum({$<CAL_YEAR={$(=max(CAL_YEAR))},CAL_DATE = {"<=$(=max(CAL_DATE))"}>} SALES))

!

!TEXT=Year To Date Last Year

=sum({$<CAL_YEAR={$(=max(CAL_YEAR)-1)},CAL_DATE = {"<=$(=addmonths(max(CAL_DATE),-12))"}>} SALES))

so I have used this in my loading script.

Where $(vFiscalMonthStart) is 8,

    YeartoDate(Date,0,$(vFiscalMonthStart))*-1 as YTD_Flag_Fiscal,

    YeartoDate(Date,-1,$(vFiscalMonthStart))*-1 as LY_YTD_Flag_Fiscal,

1. With no selects it works as expected sum(SALES * YTD_Flag_Fiscal)

But now when my user selects this year my values for last year go to zero. Is this possible that my values report current year to date (01-8-2011 to today()) and last year to date (01-8-2010 to today()). remain when selected current year?

But when the user selects last year it report a full 12 months. Ie (01-8-2010 to 31-07-2011) 

2. My Fiscal data set at present has dates unit 2020 (forecast and actuals) and I would only like to report 2011 in this case i think a system variable is needed ? year(today()) within my statement.

Please help.

Thanks

Suky

[#2 · Accepted solution]

Hi Suky

This is how I get mine to work - This is based on a fiscal year starting in Aug as per your request.... plus you will need a Previous YTD flag field set up which can be done like this:

inyeartodate(Date, today(), -1, 😎 * -1 as PFYTD     

Previous year:

if(Max(total FiscalYear)=(Year(addmonths(today(),5))),Sum({$<FiscalYear={$(=Max(total FiscalYear)-1)},PFYTD={1}>}Sales),Sum({$<FiscalYear={$(=Max(total FiscalYear)-1)}>}Sales))

Current year:

Sum({$<FiscalYear={$(=Max(total FiscalYear))}>}Sales)

This works very well for me.

I hope it helps

[rajni_batra]

what would be the script for calculation of fiscal year starting from april.

[#4]

I believe that it would be this for previous year in Set analysis.

if(Max(total FiscalYear)=(Year(addmonths(today(),9))),Sum({$<FiscalYear={$(=Max(total FiscalYear)-1)},PFYTD={1}>}Sales),Sum({$<FiscalYear={$(=Max(total FiscalYear)-1)}>}Sales))

and with the script for PYTD would be:

inyeartodate(Date, today(), -1, 4) * -1 as PFYTD

Try this... I hope it works.