topic Re: Regarding Calendar Format in QlikView

Regarding Calendar Format

dunnalahk123 — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Regarding Calendar Format

siddharth_s3 — Sat, 24 Feb 2018 13:25:25 GMT

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

Re: Regarding Calendar Format

dunnalahk123 — Sat, 24 Feb 2018 13:46:45 GMT

HI,

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

Re: Regarding Calendar Format

siddharth_s3 — Sat, 24 Feb 2018 13:53:27 GMT

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

Re: Regarding Calendar Format

dunnalahk123 — Sat, 24 Feb 2018 14:06:48 GMT

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

Re: Regarding Calendar Format

Chanty4u — Sat, 24 Feb 2018 15:51:08 GMT

Here you need fiscal month i think

Fiscal Year

Re: Regarding Calendar Format

siddharth_s3 — Sat, 24 Feb 2018 17:58:32 GMT

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