Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a Month Field in my Fact table like below
201711
201712
201801
201802
based on above format , i have to derive Fiscal year calendar format like below
like for months >>> Nov, Dec, Jan, Feb etc.
Quarter >>> Q117, Q217, Q317,Q417.
Year >>> FY17,FY18.
Can some one help me here.
Best Regards,
HK
Build something like this as a dimension table
Main Table:
MONTH(MAKEDATE(LEFT(Month,4),RIGHT(Month,2),01)) as MonthQuarterMap
Quarter Table:
LOAD * INLINE [
MonthQuarterMap, Quarter
1, Q1
2, Q2
3, Q2
4, Q2
5, Q3
6, Q3
7, Q3
8, Q4
9, Q4
10, Q4
11, Q1
12, Q1
];
MONTHNAME(MAKEDATE(LEFT(Month,4),RIGHT(Month,2),01)) as Month,
Q & CEIL(MONTH(MAKEDATE(LEFT(Month,4),RIGHT(Month,2),01))/3) & LEFT(Month,2) as Quarter,
FY&LEFT(Month,2) as Year
HI,
I guess may be some thing is not Working accordingly. may be Left and Right Function.
Yes sorry haha.
Here you go:
Q & CEIL(MONTH(MAKEDATE(LEFT(Month,4),RIGHT(Month,2),01))/3) & RIGHT(LEFT(Month,4),2) as Quarter,
FY&RIGHT(LEFT(Month,4),2) as Year
Hi Siddharth,
Yes, Thank You.
One Last point, now if i select Q1FY17 , selected months are Jan17, Feb17, March17.
but always my Calendar is starts with nov.
So Nov16 , Dec16, Jan17 is my Q117
Feb17, Mar17, Apr17 is my Q217
may17, Jun17, July17 is my Q317
Aug17, Sep17, oct17 is my Q417
nov17, Dec17, jan18 is my Q118
and so on.
But currently if i select Q117 now it is taking jan17, feb17 march17. but it should give me nov16, Dec16, jan17.
Best Regards,
Hk
Here you need fiscal month i think
Build something like this as a dimension table
Main Table:
MONTH(MAKEDATE(LEFT(Month,4),RIGHT(Month,2),01)) as MonthQuarterMap
Quarter Table:
LOAD * INLINE [
MonthQuarterMap, Quarter
1, Q1
2, Q2
3, Q2
4, Q2
5, Q3
6, Q3
7, Q3
8, Q4
9, Q4
10, Q4
11, Q1
12, Q1
];