topic Re: Financial Year to Date in QlikView

Financial Year to Date

Mon, 03 Feb 2014 12:29:48 GMT

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!

Re: Financial Year to Date

puttemans — Mon, 03 Feb 2014 12:47:53 GMT

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.

Re: Financial Year to Date

Mon, 03 Feb 2014 13:28:35 GMT

Hi,

You can solve this by using YearToDate function as below:

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

Re: Financial Year to Date

Mon, 03 Feb 2014 14:57:30 GMT

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?

Re: Financial Year to Date

puttemans — Mon, 03 Feb 2014 15:19:48 GMT

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]

Re: Financial Year to Date

Mon, 03 Feb 2014 15:42:09 GMT

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)))

Re: Financial Year to Date

Mon, 03 Feb 2014 16:03:08 GMT

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?

Re: Financial Year to Date

puttemans — Tue, 04 Feb 2014 08:20:51 GMT

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)

Re: Financial Year to Date

puttemans — Tue, 04 Feb 2014 09:36:52 GMT

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.

Re: Financial Year to Date

Tue, 04 Feb 2014 13:56:48 GMT

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,

Re: Financial Year to Date

puttemans — Tue, 04 Feb 2014 14:06:36 GMT

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.

Re: Financial Year to Date

Tue, 04 Feb 2014 16:16:43 GMT

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.

Re: Financial Year to Date

Wed, 10 Aug 2016 07:55:31 GMT

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!