Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
joeybird
Creator III
Creator III

Financial Quarter


I have the following calendar code

Calendar: DECLARE FIELD DEFINITION TAGGED '$date' Parameters first_month_of_year = 1 Fields


     Year($1) As Year Tagged '$year',
    
     if( Month($1)>=4, Year($1), Year($1) -1) as FiscalYear Tagged '$fiscalyear', 

     Month($1) as Month Tagged '$month',
    
     if( Month($1)>=4, Month($1)-3,Month($1)+9) as FiscalMonthNumber Tagged '$FiscalMonthNumber',

     Date($1) as Date Tagged ('$date', '$day'),

     Week($1) as Week Tagged '$week',

     Weekday($1) as Weekday Tagged '$weekday',
    
     WeekEnd ($1) as WeekEnd Tagged '$weekend',
    
     'Q' & Ceil(Month($1)/3)  as FinancialQuarter Tagged '$FinancialQuarter',
    
   
    

     DayNumberOfYear($1, first_month_of_year) as DayNumberOfYear Tagged ('$numeric');

DERIVE FIELDS FROM FIELDS [EventDate] USING Calendar;

but I have an issue where Q1 starts Jan, Feb, March, BUT I need Apr, May, June as Q1

Please help

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

Shouldn't that be divided by 4?

'Q' & Ceil(if( Month($1)>=4, Month($1)-3,Month($1)+9)/4)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

6 Replies
rubenmarin

Hi Joanna, you can use the same logic as previous rows to calculate the quarter:

'Q' & Ceil(if( Month($1)>=4, Month($1)-3,Month($1)+9)/3)  as FinancialQuarter Tagged '$FinancialQuarter',

Or maybe:

'Q' & Ceil(Month(AddMonths($1, -3))/3)  as FinancialQuarter Tagged '$FinancialQuarter',

joeybird
Creator III
Creator III
Author

Hiya

I keep getting the same, but little different

Q2 keeps coming up as Oct, Sept, Dec, Aug

please help

jonathandienst
Partner - Champion III
Partner - Champion III

Shouldn't that be divided by 4?

'Q' & Ceil(if( Month($1)>=4, Month($1)-3,Month($1)+9)/4)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
rubenmarin

Hi Joanna, I tried with standard Master calendar and seems to work:

Not sure wich value has your $1, PFA to check differences.

rubenmarin

That happens to me sometimes, in spanish the word 'Trimestre' refers to a 3-month block but in english 'Quarter' seems related to the year spliteed in 4 parts, leading to think in that '4' to divide, but the 12 months has to be divided by 3 to get the 4 parts.

joeybird
Creator III
Creator III
Author

Hiya

thank you

cracked it, with your help too

'Q' & Ceil(if( Month($1)>=4, Month($1)-3,Month($1)+9)/3)as FinancialQuarter Tagged '$FinancialQuarter';

Kind Regards