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

[Anonymous]

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)

[Anonymous]

Try This one

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

CALEN_DT={">=$(=MonthStart(AddMonths(Max(CALEN_DT),-11)))<$(=MonthEnd(AddMonths(Max(CALEN_DT),-0)))"}>}Value)

Regards,

Devendar

[ahaahaaha]

Can all the same such variant

1. The script downloads all data.

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

[rahulpawarb]

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