New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for
Did you mean:
Contributor II

Return fiscal quarter from a date in data loader?

Greetings. I am trying to generate a formula to return the fiscal quarter as a number from a month value in the data loader, where the fiscal quarter starts in October.

The Quarter() function would be great if we went by calendar year. I also need it to be JUST the quarter, not the year. So while Quarter() returns values of "Jan-Mar 1999" and "Oct-Dec 2020" I need just "2" and "1" respectively for those values.

In Excel, I'd just use a choose command, which would be =Choose(MonthNumberHere,2,2,2,3,3,3,4,4,4,1,1,1). This would just look at the number in the list and return that value. This function doesn't appear to be in Qlik.

The only way I see to do it right now is a long awkward series of nested If statements, and I don't want to leave that in the code for whoever comes after me.

And yes, I recognize that ideally I'd be splitting the data into multiple tables and linking each to the dates table that has this information. I'm unable to do that in this specific situation.

EDIT

I found a formula for it. CEIL is the RoundUp() function from Excel, so...

IF(CEIL(("SentMonthNum")/3)<4,CEIL(("SentMonthNum")/3)+1,1) as "InvoiceSentQuarter"
// If the month, rounded up to the nearest multiple of 3 (3,6,9,12), divided by 3 (1, 2, 3, 4) is less than four, return that value plus one, otherwise return a value of 1. Thus January (month 1) is rounded up to 3, divided by 3 is 1, and as that's less than four it returns 1+1=2, which is the fiscal quarter.

For some reason nothing is working anymore. It worked, I closed it, opened it, changed nothing and now get errors. Marking as not solution. Resident load isn't working, it's like Qlik isn't even recognizing that word.

Labels (1)
• Qlik Sense

Contributor II

Hi Robert,
Usually when you don't have an offset, you'll be using the function below:

'Q' & Ceil(month(TempDate)/3) as Quarter,
or, if you need just the Number:

Ceil(month(TempDate)/3) as Quarter,

However, if you are using an offset, another solution would be to have a mapping table with the month/quarter.. and you'll call that from the select:

QuarterMap:

[

Month,Quarter

10,Q1

11,Q1

12,Q1

1,Q2

2,Q2

3,Q2

4,Q3

5,Q3

6,Q3

7,Q4

8,Q4

9,Q4

];