topic Re: Financial Quarter in App Development

Financial Quarter

joeybird — Mon, 27 Apr 2015 11:41:45 GMT

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

Re: Financial Quarter

JonnyPoole — Mon, 27 Apr 2015 13:05:58 GMT

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

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

Re: Financial Quarter

joeybird — Mon, 27 Apr 2015 13:26:11 GMT

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

Re: Financial Quarter

ankitaag — Mon, 27 Apr 2015 13:42:33 GMT

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

And if it is fine then filter out those null values

Re: Financial Quarter

joeybird — Mon, 27 Apr 2015 13:45:11 GMT

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

Re: Financial Quarter

ankitaag — Mon, 27 Apr 2015 13:49:15 GMT

Financial Year starting from April would be like this

Year($(1),4)

Re: Financial Quarter

JonnyPoole — Mon, 27 Apr 2015 14:02:29 GMT

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;

Re: Financial Quarter

joeybird — Tue, 28 Apr 2015 10:53:46 GMT

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

Re: Financial Quarter

JonnyPoole — Tue, 28 Apr 2015 13:22:50 GMT

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

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

Re: Financial Quarter

joeybird — Tue, 28 Apr 2015 13:52:49 GMT

Hiya

cracked it this is fab!

thanx

kind regards

joeybird

Re: Financial Quarter

joeybird — Tue, 28 Apr 2015 14:40:38 GMT

Hiya

got a major problem, since this code gets added to the sorting, it crashes the app and program.

it saves and works fine, when you create the dynamic graph. you go out of the app fully and go back in and the moment it goes to the sheet with the graph with the code below, it crashes.

I have created a new sheet from scratch, test again from scratch

you add the code

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

note:OrderDate is changed for my field

save ..use sheet all works fine, exit the app and go back in, the moment you get to the sheet with the graph in, it crashes the program

help please

kind regards

joeybird

Re: Financial Quarter

JonnyPoole — Tue, 28 Apr 2015 14:56:52 GMT

could you share the app ? i do not get this behaviour myself. I could share my sample too if it would help.

ps: i'm using version 1.1

Re: Financial Quarter

joeybird — Wed, 29 Apr 2015 08:10:06 GMT

Hiya

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

I accidently renamed one of my fields wrong in the middle...

sorry least we found an error and how to avoid it if it happens again xx

Kind Regards

Joeybird