Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
Hi,
You can solve this by using YearToDate function as below:
Sum(If(YearToDate(Date#(YearMonth,'MMM-YY'),0,vFirstMonth), Revenue)
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?
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]
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)))
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?
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)
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.
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,