Financial Year Set Analysis

[Anonymous]

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

[Gysbert_Wassenaar]

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

[ahaahaaha]

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

[rahulpawarb]

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

[rahulpawarb]

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

[ahaahaaha]

Hi Rahul,

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

Regards,

Andrey

[Anonymous]

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).

Thanks in advance

Phil

[rahulpawarb]

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

[ahaahaaha]

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

[Anonymous]

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