Financial Year to Date

[#1]

I am trying to work out what is the best way of calculating total revenue to date for my financial year (July - Jun).  The financial year start date is set  in the import scrip as "SET vFirstMonth = 7;"

I've tried using the following expressions, but it only works to the calendar year:

     Sum({<[Date] = {">=$(=yearstart(DATE(MAX(TOTAL [Date]))))<=$(=DATE(MAX(TOTAL [Date])))"}>}

Revenue))

If my data is like the following, I would only want to include all revenue up until the current month (Jan-14).

YearMonth     Revenue    

Jun-13          £100

Jul-13            £200

Aug-13          £300

Sep               £400

Oct-13          £500

Nov-13          £400

Dec-13          £300

Jan-14         £400

Feb-14          £200 (Forecast)

Mar-14          £300 (Forecast)

I've looked at the other posts on this matter, but I am unable to get my head around how this can be simply achieved. 

Please help!

[puttemans]

I guess this message means that the name you gave your first table is not similar to the name I gave it (Revenue). Beware, Qlikview is case sensitive. The 'resident' operator will look for tables in memory defined by the name that follows.

You could leave year and month as they are. When you use YearMonth instead of Month in my script, it will work correct when you add new months (while else it will stop at 12). Withitn the definition of the fiscal year, the 13 should be replaced with 14 as well tp have a complete year.

[#3]

Yes, my table was in lower-case. I now have Period ID and FiscYear as dimensions, and I have built the chart with the expression.  However the result does not sum year to date, it simply skips the period ID in the expression; in the below case, month 10:

(Sum(If(YearToDate(FiscYear),0,Revenue))) - (SUM(IF(PeriodID = 10,Revenue,0)))

I am confused.

[#4]

Hi All,

Use below Expression for YTD last year and for current year YTD , please remove addmonths() and addyears() function properly:

Sum({< Date_Feild={">=$(='1-04-'&year(addyears(date(now(),'DD-MM-YYYY'),-1))) <=$(=addmonths(date(now(),'DD-MM-YYYY'),-12)) "}>}Sales)

Please Mark My comment Helpful!