Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Financial Year to Date

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!

12 Replies
puttemans
Valued Contributor

Re: Financial Year to Date

In the QV for developers book, the formula for a YTD expression is :

(Sum({$<PeriodID = {"<=$(=Max(PeriodID))"}, Year={"$(=Max(Year))"} [Revenue]

Whereby the PeriodID is a increasing autonumber from the different months (this to overcome the issue of december to jauary going from 12 to 1). The formula still takes into account the year, but I would try to make a variable like [fiscal year] and see whether it works. I guess the system works with the input you give it, and thus will work with the fiscal year the same way.

Not applicable

Re: Financial Year to Date

Hi,

You can solve this by using YearToDate function as below:

Sum(If(YearToDate(Date#(YearMonth,'MMM-YY'),0,vFirstMonth), Revenue)

Not applicable

Re: Financial Year to Date

I've previously set a FiscalYear variable from the date in the input script:

       Year (Date) as Year,

       Month (Date) as Month,

       Date(Monthstart(Date), 'MMM-YYYY') as YearMonth,

            IF(IsNum(Month(Date)),'Q' & Ceil(Month(Date),3)/3) AS Quarter,               

            year(YearName(Date, 1, 7)) as FiscalYear,            

            'Q' & ceil(month(MonthName(Date, 6)) / 3) as FiscalQuarter,   

But when added to the expression, it does not seem to return any values.

Sum({$<FiscalYear = {"<=$(=Max(FiscalYear))"}, Year={"$(=Max(Year))"} Revenue

Is this because I have not set the FiscalYear correctly?

puttemans
Valued Contributor

Re: Financial Year to Date

Hi Clive

You will not get any variables for the FiscalYear since it is calculated as a string.  (QV help : If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year. The display value will then be a string showing two years.)

I'd first use the AutoNumber ('AutoNumber(Month) as PerioID) function to give a distinct number to your different months. Remark: this is only possible if you load in a chronological order, so e.g. not first 2013 and then 2012.

From there on, you can define the fiscal year

(IF (PeriodID> x and PeriodID < y),2012) as FiscYear) --> Where x and y are the boundaries of your fiscal year, so difference between them should be 12

Then the formula would look like

(Sum({$<PeriodID = {"<=$(=Max(PeriodID))"}, FiscYear={"$(=Max(FiscYear))"} [Revenue]

Not applicable

Re: Financial Year to Date

This works but I would would like to exclude the current month.  I've tried the following expression (inserting a "-1"), but I think excludes the current year. 

(Sum(If(YearToDate(Date#(YearMonth,'MMM-YY'),-1,vFirstMonth), Revenue)))

Not applicable

Re: Financial Year to Date

Hi Johan,

Apologies, I am a bit of a newbie here, so you'll need bear with me. 

I am not sure what code to add and where.  Do I need to set and values in the script, or should I add this to the load data?

puttemans
Valued Contributor

Re: Financial Year to Date

The autonumbering and the definition of your fiscal year should be done in  the scripting window. This needs to be in 2 steps, as you first define the number, and then work with the number to define the fiscyear.

Then the below formula would be for your charting:

(Sum({$<PeriodID = {"<=$(=Max(PeriodID),2)"}, FiscYear={"$(=Max(FiscYear))"} [Revenue]

By adding the ',2', QV will look not for the highest value, but the second highest (i.e. not current but previous month)

puttemans
Valued Contributor

Re: Financial Year to Date

Hi Clive,

I've made a simulation myself. Here's the code I used in the script editor :

Revenue:

LOAD YearMonth,
Revenue,
Year(YearMonth) as Year,
Month(YearMonth) as Month
FROM
C:\Users\rfn7501\Desktop\trash\Test.xls
(
biff, embedded labels, table is [Sheet1$]);

Step1:
Load *,
AutoNumber (Month) as PeriodID
Resident Revenue;
Drop table Revenue;

Step2:
Load *,
IF ((PeriodID> 1 and PeriodID < 13),2013) as FiscYear
Resident Step1;
Drop table Step1;

Then, in the properties window from the chart, you enter 'FiscYear' as a dimension. and  '=(Sum(If(YearToDate(FiscYear),0,Revenue))) - (SUM(IF(PeriodID = 10,Revenue,0))) ' as expression. This will give you YTD figures without the last month of the period. This will work on the sample you have, you may need to redefine de period in case of another fiscal year.

Not applicable

Re: Financial Year to Date

I've added the this to the script, but I get the following when I try to run the script. 

Table not found

Load *,

AutoNumber (Month) as PeriodID

Resident Revenue

I'm also not sure about the changing YearMonth back to month and year, as YearMonth comes from the date column already.  See below:

       Year (Date) as Year,

       Month (Date) as Month,

       Date(Monthstart(Date), 'MMM-YYYY') as YearMonth,

            IF(IsNum(Month(Date)),'Q' & Ceil(Month(Date),3)/3) AS Quarter,              

            year(YearName(Date, 1, 7)) as FiscalYear,           

            'Q' & ceil(month(MonthName(Date, 6)) / 3) as FiscalQuarter,