Financial Quarter

[joeybird]

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

[JonnyPoole]

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

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

[joeybird]

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]

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]

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]

Hi

Financial Year starting from April would be like this

Year($(1),4)

[JonnyPoole · Accepted solution]

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;

[joeybird]

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]

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

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

[joeybird]

Hiya

cracked it this is fab!

thanx

kind regards

joeybird