13 Replies Latest reply: Feb 13, 2017 5:09 AM by Rahul Pawar

# Financial Year Set Analysis

Hi

I need to return data in set analysis for this financial year only (April to March).

I have a field that shows which financial Year the data is in [Financial Year].

How do I create set analysis which returns a value only if we are in the current financial year.

e.g.  Only sums data if today is within financial year 2016 (any data between April 2016 - March 2017)

Thoughts?

Thanks

Phil

• ###### Re: Financial Year Set Analysis

Perhaps something like this: sum({<Year=,Month=, [Financial Year]={2017}>}Amount)

• ###### Re: Financial Year Set Analysis

Hi Phil,

If select not all the dates of the one value of the field [Financial Year], it may as a variant of use the "Date", for example

=Sum({<Date={">01.04.2016"}*{"<31.03.2017"}>} Volume)

Regards,

Andrey

• ###### Re: Financial Year Set Analysis

Hello Andrey,

I like the solution; however, it would be great if we include the boundary values as well.

=Sum({<Date={">=01.04.2016"}*{"=<31.03.2017"}>} Volume)

Please correct me if I misunderstood.

Thank you!

Rahul

• ###### Re: Financial Year Set Analysis

Hi Rahul,

Yes, of course you're right. I just showed you how combine the sets.

Regards,

Andrey

• ###### Re: Financial Year Set Analysis

Hello Phil,

Please refer below given draft expression:

Sum({<FiscalYear={"=If(Today() >= Date#('04/01/2016', 'MM/DD/YYYY') AND Today() <= Date#('03/31/2017', 'MM/DD/YYYY'), If(Month(Today())<=3, Year(Today())-1, Year(Today())))"}>}Sales)

Hope this will be of help.

Regards!

Rahul

• ###### Re: Financial Year Set Analysis

All these are good, but none of them will automatically pick up the previous year, as I will have to change the date in the script every new financial year.

e.g.  I need the financial year 2016/17 to show 2015/16 data.  Then when we move into 2017/18 I need it to show 2016/17 data.

In summary it always needs to show the previous year (future proofed).

Phil

• ###### Re: Financial Year Set Analysis

Hello Phil,

Please refer below given draft expression:

Sum({<FiscalYear={"=If(Today() >= MakeDate(Year(Today())-1,4,1) AND Today() <= MakeDate(Year(Today()),3,31), If(Month(Today())<=3, Year(Today())-1, Year(Today())))"}>}Sales)

In above expression,

MakeDate(Year(Today())-1,4,1)  - will return lower bound/start point i.e. 04/01/2016

MakeDate(Year(Today()),3,31) - will return upper bound/end point i.e. 03/31/2017

Hope this will help.

Regards!

Rahul

• ###### Re: Financial Year Set Analysis

This works, nice one.

Tried to use the same formula for current year.  Made slight amendments, but didn't work.  Any thoughts?  See below

Sum({<FiscalYear={"=If(Today() >= MakeDate(Year(Today())-0,4,1) AND Today() <= MakeDate(Year(Today()),3,31), If(Month(Today())<=3, Year(Today())-0, Year(Today())))"}>}Sales)

• ###### Re: Financial Year Set Analysis

Hello Phil,

Could you please try below given sample expression:

Sum({<FiscalYear={"=If(Today() >= If(Month(Today())<=3, MakeDate(Year(Today())-1,4,1), MakeDate(Year(Today()),4,1)) AND Today() <= If(Month(Today())<=3, MakeDate(Year(Today()),3,31), MakeDate(Year(Today())+1,3,31)), If(Month(Today())<=3, Year(Today())-1, Year(Today())))"}>}Sales)

P.S.: To reduce the length of expression you can make use of variables.

Regards!

Rahul

• ###### Re: Financial Year Set Analysis

Hi Phil,

Why do you load the script data separately for years? Maybe load data for all years, and then make selections in the charts?

Regards,

Andrey

• ###### Re: Financial Year Set Analysis

My table needs to show a column for current year and another one for last year.

It would be possible to create a set analysis which I can set specifically for 2016 or 2017, but then as we move from year to year I will have to update all my scripts to change the year.  As my dashboard is quite large it is likely it will miss the change over.

I hope that makes sense.

Thanks

Phil

• ###### Re: Financial Year Set Analysis

Can all the same such variant

2. By using the calendar wizard, each record is linked to the financial year.

3. On the chart displays the data, which are calculated for example on the basis of the current date (ie the financial year is automatically determined by calculation).

Actually approximately the same idea proposed by Rahul above.

Andrey

• ###### Re: Financial Year Set Analysis

Try This one

=Sum({\$<Month=, Year=,