Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
RobertPatrician
Contributor II
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)
1 Reply
Nikos
Contributor II
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:

mapping Load * inline

[

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

];

 

load

*,

ApplyMap('QuarterMap', month(TempDate),'n/a') as Quarter /* added 'n/a' just in case as option if month# is not found on the mapping table... */

from yourTable