topic How to display Fiscal Quarter in the format of F4 - 2018, F1- 2019 , F2-2019? in QlikView

How to display Fiscal Quarter in the format of F4 - 2018, F1- 2019 , F2-2019?

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

Hi All,

I have requirement to show the Fiscal Quarters in the below format

For Jan - Mar 2018 = F4 - 2018
For Apr - June 2018 = F1 - 2019
For July - Sept 2018 = F2 - 2019
For Oct - Dec 2018 = F3 - 2019

Please help me to obtain this format in Qlikview. Currently, I have below syntax which shows the Quarters correctly however, the YYYY part is missing and yes I do want to see 2019 from Apr to Dec of 2018 year.

'Q' & Ceil(Month(Relevant_Date)/3),
'Q' & alt (if(Month(Relevant_Date)<4,4),if(Month(Relevant_Date)<7,1),if(Month
(Relevant_Date)<10,2),3) as Quarter,

Re: How to display Fiscal Quarter in the format of F4 - 2018, F1- 2019 , F2-2019?

uacg0009 — Tue, 18 Dec 2018 06:36:38 GMT

Hi Arun,
The first idea occurred to me is using hard-code. I don't know whether you wanna use or not.
Like:
If(Relevant_Date >= '2018-01-01' and Relevant_Date < '2018-04-01','F4 - 2018',
if(Relevant_Date >= '2018-04-01' and Relevant_Date < '2018-07-01','F1 - 2019',
.....

Thanks.
Aiolos

Re: How to display Fiscal Quarter in the format of F4 - 2018, F1- 2019 , F2-2019?

arunqlik2view — Tue, 18 Dec 2018 08:59:37 GMT

Hi Aiolos,

Thanks for the reply. Yes, I don't want to use the hard code way. I looking forward to use the syntax in the Qlikview Editor, so That I can use it in Dimensions whenever I want them .

Thanks,

Arun

Re: How to display Fiscal Quarter in the format of F4 - 2018, F1- 2019 , F2-2019?

uacg0009 — Tue, 18 Dec 2018 09:16:39 GMT

Hi,
I don't try it in my scripts, but I think it will work, please try:
if(month(Relevant_Date) < 4,'F4 - ' & Year(Relevant_Date),
if(month(Relevant_Date) < 7,'F1 - ' & Year(Relevant_Date) + 1,
if(month(Relevant_Date) < 10,'F2 - ' & Year(Relevant_Date) + 1,
'F3 - ' & Year(Relevant_Date) + 1)))

Aiolos

Re: How to display Fiscal Quarter in the format of F4 - 2018, F1- 2019 , F2-2019?

santhiqlik — Tue, 18 Dec 2018 09:30:53 GMT

Hi,
I hope this should work for you !!
'F' & (Month(AddMonths(QuarterStart(Relevant_Date), -1)) / 3) & ' - ' & Year(AddMonths(MonthStart(Relevant_Date), 9))

Re: How to display Fiscal Quarter in the format of F4 - 2018, F1- 2019 , F2-2019?

libinanto95 — Tue, 18 Dec 2018 09:59:36 GMT

Hi Arun,

Trying using the following Script

LOAD
Relevant_Date,
'Q' & Ceil (Month(Relevant_Date)/3) as Quarter,
If(Month(Relevant_Date)<4,'F4'&'-'&Year(Relevant_Date),
If(Month(Relevant_Date)<7,'F1'&'-'&Year(AddYears(Relevant_Date,+1)),
If(Month(Relevant_Date)<10,'F2'&'-'&Year(AddYears(Relevant_Date,+1)),'F3'&'-'&Year(AddYears(Relevant_Date,+1)))))as Fiscal Calendar

Re: How to display Fiscal Quarter in the format of F4 - 2018, F1- 2019 , F2-2019?

arunqlik2view — Wed, 19 Dec 2018 05:00:42 GMT

Hi ,

Thank for your solution. Your syntax worked for me.

If(Month(Relevant_Date)<7,'F1'&'-'&Year(AddYears(Relevant_Date,+1)),
If(Month(Relevant_Date)<10,'F2'&'-'&Year(AddYears(Relevant_Date,+1)),'F3'&'-'&Year(AddYears(Relevant_Date,+1)))))as Fiscal Calendar

Thanks & Regards,

Arun

Re: How to display Fiscal Quarter in the format of F4 - 2018, F1- 2019 , F2-2019?

arunqlik2view — Wed, 19 Dec 2018 05:04:53 GMT

Hi , Thanks for the reply. I really appreciate the efforts you have taken.
I tried this in my Dimension section and it did not work for me. Please let me if know I should be using this syntax somewhere else.

Also, please note that my issue got fixed by using the following syntax in the QVW editor which was provided by another contributor like you.

Once again thanks for all the efforts.

Thanks & regards,

Arun