Formatting financial year for use in calculations

[#1]

Hi All,

Im having a problem calculating sales from my financial date calendar

Below is the Calendar script I have setup with help from the forums. It all works fine, until I try to Calculate Totals based on FY (financial Year)

For example in my chart if I use the Year for calculation

sum( {$<Year = {$(#=Only(Year-1))},Week =,FiscalWeek =, Day=>} CURSALES) my figures are fine

However, I need to use the Financial Year which doesn’t give me any figures

sum( {$<FY= {$(#=Only(FY-1))},Week =,FiscalWeek =, Day=, Year=>} CURSALES)

therefor I’m assuming the formatting in the load script for FY is incorrect?

Load script

TempCalendar:
LOAD Date(makedate(2011)+recno()-1) as TempDate AutoGenerate 6*  365;


MasterCalendar:
LOAD *,
  FiscalWeek &'-'& Year(Yearend(Weekstart(CalendarDate),0,6)) as WeekFY;
Load
  TempDate AS CalendarDate,
  week(TempDate) As Week,
  YEAR(TempDate) As Year,
  Month(TempDate) As Month,
  Day(TempDate) As Day,
  YeartoDate(TempDate)*-1 as CurYTDFlag,
  YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
  date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
  Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
  WeekDay(TempDate) as WeekDay,
  Date(yearend(TempDate,0,6),'YYYY') as FY,
  Date(yearend(TempDate,0,6),) as FY2,
  autonumber(Weekstart(TempDate), yearstart(Weekstart(TempDate),0,6) )   as FiscalWeek

Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

[juleshartley]

Think this is because year is an int (both the field in your case and the function), whereas FY is a date, even though you're returning it in the YYYY format, which looks like an int.

You'll need to either decide to treat year and FY as Int's or switch both to work as dates and not use the # in the set analysis (and probably use variables).

Hope that helps!
J