Sum previous end of fiscal year when a date is selected

[alanmcgrath]

Hi All,

I have an application where a user can select a date to view various metrics.  When the user selects a date I want to be able to sum (in this case the headcount) for the ending month of the previous fiscal year (8/31/Year).

I'm using a variable (vPrevYearRD) to calculate the previous year based off the selection year and then using that variable in set analysis to get the calculation.  This works fine when I'm looking at the previous year from the date using the formula =AddYears(^ReportDate,-1).  However when I try to force this to 8/31 it no longer works. MakeDate(Year(AddYears(^ReportDate,-1)),8,31).  This variable calculates to the correct date however, when I use that variable in set analysis it doesn't calculate.  Is there another way to do this or is my syntax in the set analysis incorrect??

Here is my expression with set analysis:

Sum({<^ReportDate, Date={'$(vPrevYearRD)'}>}HeadcountFTE)

I'm calling out ^ReportDate first to ignore it since I have made a selection and then using my other date field Date to equal the variable date.  So for example if I select the ^ReportDate of 9/30/2016 I want to calculate the headcount for 8/31/2015.

As I said the variable vPrevYearRD works with both the simple last year calculation and the 8/31 calculation but only the simple last year calculation works in the formula to calculate the Headcount.

Any help and advise is greatly appreciated.

Thanks!

[sunny_talwar]

May be throw in the Date() function around MakeDate()

Date(MakeDate(Year(AddYears(^ReportDate,-1)),8,31))

[alanmcgrath]

unfortunately that doesn't work either.  thanks

[sunny_talwar]

Did you try this directly into your expression?

=AddYears(^ReportDate,-1)

If you did, try adding an equal sign to see if that works

Sum({<^ReportDate, Date={'$(=vPrevYearRD)'}>}HeadcountFTE)

[alanmcgrath]

Yes, using just AddYears in my expression as well as the variable works fine, however that give me the previous year of the selection made in ^ReporDate.  The requirement is to get the previous end of fiscal year from ReportDate selected.  So if I select Sept 2016 I need to see August 2015 not Sept 2015.

I tried adding the = to the expression before the variable as you show but I still get 0 as a result.  It seems to not like that 8/31 and I can't figure out why

[sunny_talwar]

One thing, are you selecting a single ^ReportDate? If you have multiple dates selected, you might need Max() function.

Date(MakeDate(Year(AddYears(Max(^ReportDate),-1)),8,31))

I guess all of these are guess work, but I don't see anything wrong with what you have. Would you be able to share a sample?

[alanmcgrath]

I just created a dummy app and it works fine there.  I guess maybe it is another problem with my app.