Year to date Set Analysis

[Anonymous]

I have the following set analysis for calculating Year to Date based on calendar year:

where MonthID is calculated as follows:

i have a fiscal month variable which is vFM = 6 denoting when the month the fiscal year starts. How can this be adjusted to come to a Year to Date based on the fiscal year calendar?

Regards.

[sunny_talwar]

May be something like this in the script

AutoNumber(Mod(Month(Date) - $(vFM), 12)+1) as FiscalMonthID

Num(Mod(Month(Date) - $(vFM), 12)+1) as FiscalMonthID

NOTE: Grabbed the logic from here

Fiscal and Standard Calendar generation

[Anonymous]

Many thanks Sunny

The recommendation above gives the same value as the calendar Year to Date

Regards

[sunny_talwar]

How about something like this:

Sum({<Year, Quarter, Month, MonthID, Date = {"$(='>=' & Date(YearStart(Max(Date), 0, 6), 'YourDateFieldFormatHere') & '<=' & Date(Max(Date), 'YourDateFieldFormatHere'))"}>} Amount)

[Anonymous]

Sunny the moment i enter the expression, i get a message 'Error in expression'

[sunny_talwar]

Can you share a screenshot? In the mean time I can check the syntax.

[sunny_talwar]

Sum({<Year, Quarter, Month, MonthID, Date = {"$(='>=' & Date(YearStart(Max(Date), 0, 6), 'YourDateFieldFormatHere') & '<=' & Date(Max(Date), 'YourDateFieldFormatHere'))"}>} Amount)

Did you replace YourDateFieldFormatHere with your datefield's format or did you just copy and paste the whole thing?

[Anonymous]

I replaced it with 'MM/DD/YYYY'

[sunny_talwar]

Can you share a screenshot of the error? Did you try clicking okay to see if you still see any result or not?

[Anonymous]

Many thanks, i had not click ok, when i do so i get the following result:

However the calculated Year to Date is 8 132 064.83