Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cancel
Showing results for 
Search instead for 
Did you mean: 
joeybird
Creator III
Creator III

Financial Quarter

hiya

I am using the following in my load script using Date_Received... in order to get my month, year ect fields

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

     Year($1) As Year Tagged '$year',

     Month($1) as Month Tagged '$month',

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

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

     Weekday($1) as Weekday Tagged '$weekday',

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

DERIVE FIELDS FROM FIELDS [Date_Received] USING Calendar;

how do I amend the code / add code above to get financial quarters please?

e.g Q1 = Jan to March

then how do I get financial year ....April 1st 2012 to March 31st of e.g. 2013

Please help

Kind Regards

Joeybird

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

Here is a sample that has a few more fiscal year fields  (FiscalYear, FiscalMonthNumber,FiscalYearMonth , FiscalDayNumberofyear)... you can add as many as you want.

If you want to show a bar chart that begins with April,  use  Month as the dimension, but under sort , sort 'by expression' and choos 'fiscalmonthnumber' as the sort .

Calendar:

DECLARE FIELD DEFINITION TAGGED '$date'

   Parameters

      first_month_of_year = 1,

      fiscal_first_month_of_year = 4

   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',

      if( Month($1)>=4, Year($1), Year($1) -1) & ' - ' & if( Month($1)>=4, Month($1)-3,Month($1)+9) as FiscalYearMonth tagged '$fiscalyearmonth',    

    

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

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

      Weekday($1) as Weekday Tagged '$weekday',

     

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

      DayNumberOfYear($1, fiscal_first_month_of_year) as FiscalDayNumberOfYear Tagged ('$numeric');

DERIVE FIELDS FROM FIELDS OrderDate USING Calendar;

Capture.PNG

View solution in original post

12 Replies
JonnyPoole
Employee
Employee

For the first question , you can add a row to the table as follows. 

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

joeybird
Creator III
Creator III
Author

Hiya

Thank you

it does work for question part 1

however I have a random extra single Q in my drop down menu

Q1,Q2,Q3,Q4 & Q

Kind regards

joeybird x

ankitaag
Partner
Partner

That is may be because you don't have date infrom of them..

And if it is fine then filter out those null values

joeybird
Creator III
Creator III
Author

Hiya

makes sense now...

understandable.... kool question part 1 completed...

can you both please help on how to get part 2 to work please?

..... how do I get financial year ....April 1st 2012 to March 31st of e.g. 2013

kind regards

joeybird

ankitaag
Partner
Partner

Hi

Financial Year starting from April would be like this

Year($(1),4)

JonnyPoole
Employee
Employee

Here is a sample that has a few more fiscal year fields  (FiscalYear, FiscalMonthNumber,FiscalYearMonth , FiscalDayNumberofyear)... you can add as many as you want.

If you want to show a bar chart that begins with April,  use  Month as the dimension, but under sort , sort 'by expression' and choos 'fiscalmonthnumber' as the sort .

Calendar:

DECLARE FIELD DEFINITION TAGGED '$date'

   Parameters

      first_month_of_year = 1,

      fiscal_first_month_of_year = 4

   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',

      if( Month($1)>=4, Year($1), Year($1) -1) & ' - ' & if( Month($1)>=4, Month($1)-3,Month($1)+9) as FiscalYearMonth tagged '$fiscalyearmonth',    

    

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

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

      Weekday($1) as Weekday Tagged '$weekday',

     

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

      DayNumberOfYear($1, fiscal_first_month_of_year) as FiscalDayNumberOfYear Tagged ('$numeric');

DERIVE FIELDS FROM FIELDS OrderDate USING Calendar;

Capture.PNG

joeybird
Creator III
Creator III
Author


Hiya

this is now working brilliantly, issue I have is I understand how to get the sorting done for fiscal year... issue I have is a person can just want to pick a normal year e.g 2014  ...I would want this to load from jan to dec,

is there a way, if a person picks 'year' it will run jan to dec

but if the person picks 'fiscal year' sorting will dynamically change to apr to mar?

Please x

Kind Regards

Joeybird 

JonnyPoole
Employee
Employee

on the 'sort by expression' try the following expression:

if (getselectedcount([OrderDate.Calendar.FiscalYear])>0, [OrderDate.Calendar.FiscalMonthNumber], [OrderDate.Calendar.Month])

joeybird
Creator III
Creator III
Author

Hiya

cracked it this is fab!

thanx

kind regards

joeybird