calendar yr to indian financial yr and Quarter

[kunkumnaveen]

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

[sunny_talwar]

May be create a fiscal year calendar: Fiscal and Standard Calendar generation

[kunkumnaveen]

Is it just copy and paste above code

[Anonymous]

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

[sunny_talwar]

I think the only thing you have to change would be this

SET vFiscalYearStartMonth = 4;

Change this to your first fiscal month number

[Anonymous]

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

[kunkumnaveen]

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 ?

[Anonymous]

Yes

[Anonymous]

Is you Date_Of_Review in Date Format.?

If not you have to first correct its format....

[kunkumnaveen]

what you mean by date format , by date look like this 2004-01-15