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.
