YTD

[lionking15]

HI

i wand to create YTD but according to fiscal year that starts from "April" .suppose i am having three fields(book_id,date,customer,cost) and i want to do sum of cost but according to YTD.

EX- if i select date '02/02/2016' then my sum of cost should be from(01/04/2015) to (02/02/2016)

Thanks!!!!!!!!!!

[Anonymous]

Hi Giridhar,

The best way to do it is to have Real Date and Fiscal Date. Add to your Real Date -3 months to get Fiscal Date,

LOAD

     Date,

     Month(Date) as Month,

     Year(Date)    as Year,

     Month(AddMonths(MonthStart(Date),-3)) as FiscalMonth,

     Year(AddMonths(MonthStart(Date),-3)) as FiscalYear

     ...

RESIDENT ...

Then, calculate your YTD with FiscalYear, not with RealYear.

Regards!!!

[#3]

Here - Sum({1 <date={">=$(=YearStart(max(date), 0, 4))<=$(=max(date))"}>} cost):

Data:
LOAD
  Floor(Rand() * 100) as book_id
  , Date(Floor(YearStart(Today()) + Rand() * (Today() - YearStart(Today())))) as date
  , Floor(Rand() * 1000) as customer
  , num(Rand() * 100, '####.00') as cost
AutoGenerate 1000;


Calendar:
LOAD
  date
  , Year(date) as dmYear
  , Month(date) as dmMonth
  , Day(date) as dmDay
  , YearStart(date, 0, 4) as FinancialYearStart
;
LOAD
  MinDate + IterNo() - 1 as date
While
  MinDate + IterNo() - 1 <= MaxDate
;
LOAD
  Min(date) as MinDate
  , Max(date) as MaxDate
Resident Data;

And see attachement.

[Anonymous]

Hi Nick,

Your calendar:

Calendar:
LOAD
    date
    , Year(date) as dmYear
    , Month(date) as dmMonth
    , Day(date) as dmDay
    , YearStart(date, 0, 4) as FinancialYearStart
    , Year(Addmonths(Monthstart(date),-3)) as FiYear
    , Month(Addmonths(Monthstart(date),-3)) as FiMonth
    , AddMonths(date,-3) as FiDate
    , MonthName(Addmonths(Monthstart(date),-3)) as FiMonthName
;
LOAD
    MinDate + IterNo() - 1 as date
While 
    MinDate + IterNo() - 1 <= MaxDate
;
LOAD
    Min(date) as MinDate
    , Max(date) as MaxDate
Resident Data;

Your YTD Expression based on Fiscal Calendar:

Sum({<dmYear=, dmMonth=, dmDay=, FiYear = {$(=max(FiYear))}, FiMonth = {"<=$(=max({<FiYear={$(=max(FiYear))}>} FiMonth))"}>} cost)

Regards!!!

[#5]

Manuel, it was an answer, not a question. Working example was attached with more simple approache.