Fiscal YTD Sum

nwilliams — Wed, 08 Jul 2015 15:01:35 GMT

Hi,

I'm trying to create a sum to only add up figures based on the Month, and the relevant fiscal year.

The Fiscal year start on 1st June. So if a user selects "Sep 2014" they would see the sum of planned cost for 01/01/2014 > 30/09/2014

Where as if they select Jun 2015, the would see figures from 01/06/2015 - 31/06/2015

The user select using the list box created by the "MonthName" function so it reutrns the 3 letter month, and the year.

I have create a variable to select the first month in the fiscal year, but this doesn't appear to work, as if you select "Jan 2015" I would expect it to return 01/06/2014 as that was the start of the fiscal year "Jan 2015" is in. The variable is:

=addmonths(YearStart(Calendar.MonthName),5)

Any ideas? I see there are plenty of suggestions out there, and they appear to work if you are using standard calendar year.

The formula I'm using to get the sum is: sum({<Calendar.MonthName = {">=$(vFYTDStart)<=$(vFYTDEnd)"}>}Finacial_Data_Pln.Cost)

Re: Fiscal YTD Sum

Wed, 08 Jul 2015 17:06:53 GMT

This is formula for vFYTDStart:

=IF(Month(Calendar.MonthName)<6,MakeDate(Year(Calendar.MonthName)-1,6,1),MakeDate(Year(Calendar.MonthName),6,1))

topic Fiscal YTD Sum in QlikView

Fiscal YTD Sum

Re: Fiscal YTD Sum