Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
which function do i need to use to achieve this
jan
feb
mar as Q4 2015
apr
may
jun as Q1 2016
July
aug
sep as Q2 2016
oct
nov
dec as Q3 2016
and actual/calendar year is 2015
current my expression is this which is not good
'Q'&ceil(MONTH(DATE)/3) AS QUATERS,
year(DATE) as YEAR
thanks
naveen
May be create a fiscal year calendar: Fiscal and Standard Calendar generation
Is it just copy and paste above code
Try this,
Suppose Yous are have Actual Date in You Table,
Then ,
Fiscal Year >> yearstart (Actual_Date, 0, 4) as Fiscal_Year,
Fiscal Quarter >> pick(match(month(Actual_Date),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),
'Q4','Q4','Q4','Q1','Q1','Q1','Q2','Q2','Q2','Q3','Q3','Q3') as Fiscal_Quarter
Fiscal Month >> Month(Actual_Date) as Fiscal_Month.
Regards
Nitin
I think the only thing you have to change would be this
SET vFiscalYearStartMonth = 4;
Change this to your first fiscal month number
QuartersMap:
Mapping LOAD
RowNo() as Month,
'Q' & Ceil (RowNo()/3) as Quarter
AutoGenerate (12);
Temp:
LOAD
min(OrderDate) as minDate,
max(OrderDate) as maxDate
Resident ORDER;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
TempCalendar:
LOAD
$(varMinDate) + IterNo()-1 as Num,
Date($(varMinDate) +IterNo()-1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo()-1 <= $(varMaxDate);
DROP Table Temp;
OrderCalendar:
LOAD
TempDate as OrderDate,
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
ApplyMap('QuartersMap', Month(TempDate), Null()) as Quarter,
Week(WeekStart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order by TempDate asc;
Drop Table TempCalendar;
try this code for standard code for finical year any one and you can change the table name at the minmax table
i am getting this o/p
after writing this expression
pick(match(month(DATE_OF_REVIEW),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),
'Q4','Q4','Q4','Q1','Q1','Q1','Q2','Q2','Q2','Q3','Q3','Q3') as QUATERS,
Year(yearstart (DATE_OF_REVIEW, 0, 4)) as YEAR
is this right ?
Yes
Is you Date_Of_Review in Date Format.?
If not you have to first correct its format....
what you mean by date format , by date look like this 2004-01-15