Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dunnalahk123
Creator III
Creator III

Regarding Calendar Format

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

1 Solution

Accepted Solutions
siddharth_s3
Partner - Creator II
Partner - Creator II

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
]
;

View solution in original post

6 Replies
siddharth_s3
Partner - Creator II
Partner - Creator II

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

dunnalahk123
Creator III
Creator III
Author

HI,

I guess may be some thing is not Working accordingly. may be Left and Right Function.

Calendaryear.PNG

siddharth_s3
Partner - Creator II
Partner - Creator II

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

dunnalahk123
Creator III
Creator III
Author

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

Chanty4u
MVP
MVP

Here you need fiscal month i think

Fiscal Year

siddharth_s3
Partner - Creator II
Partner - Creator II

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
]
;