Let's Get Social!
Focal Point    Focal Point Forums    WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Variance and forecast Calculation
 Go New Search Notify Tools Reply
 [CLOSED] Variance and forecast Calculation
Silver Member
 posted September 16, 2014 10:52 AM
Hi,

I am trying to find the variance of a SUMMED field based on an Across (fiscal year) and By (month), as well as forecast based on the previous year's data.

For the variance column, I was thinking of doing a compute field, in which I simply take last year's month - this year's month (ie. July 2014 minus July 2013 number) but I have been unable to use two different fields to calculate.

For the forecast, I was thinking of filling in "the rest of the fiscal year" based on last year's numbers - ie. (Volume this year April to June) * (Volume Last Year April To September) / (Volume Last Year April to June)

My code is as follow:
JOIN
IN PROD_FACT_3M_DAD_IP_WEIGHTED_CASES TO MULTIPLE DIM_DATE.DIM_DATE.DATE
IN DIM_DATE TAG J0 AS J0
END
Fiscal_year/A12=
IF PROD_FACT_3M_DAD_IP_WEIGHTED_CASES.FACT_3M_DAD_IP_WEIGHTED_CASES.DISCHARGE_DATE LE '2013-04-01' THEN '2012-2013' ELSE
IF PROD_FACT_3M_DAD_IP_WEIGHTED_CASES.FACT_3M_DAD_IP_WEIGHTED_CASES.DISCHARGE_DATE LE '2014-04-01' THEN '2013-2014' ELSE
END
SUM
BY LOWEST J0.DIM_DATE.FISCAL_MONTH_OF_YEAR_NAME AS 'Month' ROWS 'Apr' OVER 'May' OVER 'Jun' OVER 'Jul' OVER 'Aug' OVER 'Sep' OVER 'Oct' OVER 'Nov' OVER 'Dec' OVER 'Jan' OVER 'Feb' OVER 'Mar'

This message has been edited. Last edited by: <Kathryn Henning>,

8.0.0.2
Windows, All Outputs

 Posts: 41 | Registered: February 27, 2014 IP
Expert
 posted September 16, 2014 11:09 AM Hide Post
Have you thought about using FML?

Many posts abound in the forum.

T

 In FOCUSsince 1986 WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 WebFOCUS App Studio 8.2.06 standalone on Windows 10

 Posts: 5681 | Location: United Kingdom | Registered: April 08, 2004 IP
Silver Member
 posted September 16, 2014 11:25 AM Hide Post
Hi, Thanks, what is FML referring to?

8.0.0.2
Windows, All Outputs

 Posts: 41 | Registered: February 27, 2014 IP
Virtuoso
 posted September 16, 2014 11:35 AM Hide Post
FML : "Financial Modeling Language" module of IBI

WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007

 Posts: 2327 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013 IP
Expert
 posted September 16, 2014 12:27 PM Hide Post
Here's an example using the GGSALES sample data -

```SET NODATA = 0
DEFINE FILE GGSALES
FISCAL_MONTH_OF_YEAR_NAME/Mt = DATE;
Year/YY       = DATE;
Fiscal_year/A12 = IF DATE LT '19960401' THEN '2012-2013' ELSE
IF DATE LT '19970401' THEN '2013-2014' ELSE
IF DATE LT '19980401' THEN '2014-2015';
END
TABLE FILE GGSALES
SUM CNT.ST   AS 'Cases'
DOLLARS
COMPUTE YOY/D8c = 0; AS 'YoY'
ACROSS Fiscal_year
-*   BY LOWEST FISCAL_MONTH_OF_YEAR_NAME AS 'Month' ROWS 'Apr' OVER 'May' OVER 'Jun' OVER 'Jul' OVER 'Aug' OVER 'Sep' OVER 'Oct' OVER 'Nov' OVER 'Dec' OVER 'Jan' OVER 'Feb' OVER 'Mar'
FOR FISCAL_MONTH_OF_YEAR_NAME
'Apr' LABEL 'APR' OVER
'May' LABEL 'MAY' OVER
'Jun' LABEL 'JUN' OVER
'Jul' LABEL 'JUL' OVER
'Aug' LABEL 'AUG' OVER
'Sep' LABEL 'SEP' OVER
'Oct' LABEL 'OCT' OVER
'Nov' LABEL 'NOV' OVER
'Dec' LABEL 'DEC' OVER
'Jan' LABEL 'JAN' OVER
'Feb' LABEL 'FEB' OVER
'Mar' LABEL 'MAR' OVER
RECAP APR(3,*,3) = IF APR(*-1) EQ 0 OR APR(*-4) EQ 0 THEN 0 ELSE APR(*-4) - APR(*-1); OVER
RECAP MAY(3,*,3) = IF MAY(*-1) EQ 0 OR MAY(*-4) EQ 0 THEN 0 ELSE MAY(*-4) - MAY(*-1); OVER
RECAP JUN(3,*,3) = IF JUN(*-1) EQ 0 OR JUN(*-4) EQ 0 THEN 0 ELSE JUN(*-4) - JUN(*-1); OVER
RECAP JUL(3,*,3) = IF JUL(*-1) EQ 0 OR JUL(*-4) EQ 0 THEN 0 ELSE JUL(*-4) - JUL(*-1); OVER
RECAP AUG(3,*,3) = IF AUG(*-1) EQ 0 OR AUG(*-4) EQ 0 THEN 0 ELSE AUG(*-4) - AUG(*-1); OVER
RECAP SEP(3,*,3) = IF SEP(*-1) EQ 0 OR SEP(*-4) EQ 0 THEN 0 ELSE SEP(*-4) - SEP(*-1); OVER
RECAP OCT(3,*,3) = IF OCT(*-1) EQ 0 OR OCT(*-4) EQ 0 THEN 0 ELSE OCT(*-4) - OCT(*-1); OVER
RECAP NOV(3,*,3) = IF NOV(*-1) EQ 0 OR NOV(*-4) EQ 0 THEN 0 ELSE NOV(*-4) - NOV(*-1); OVER
RECAP DEC(3,*,3) = IF DEC(*-1) EQ 0 OR DEC(*-4) EQ 0 THEN 0 ELSE DEC(*-4) - DEC(*-1); OVER
RECAP JAN(3,*,3) = IF JAN(*-1) EQ 0 OR JAN(*-4) EQ 0 THEN 0 ELSE JAN(*-4) - JAN(*-1); OVER
RECAP FEB(3,*,3) = IF FEB(*-1) EQ 0 OR FEB(*-4) EQ 0 THEN 0 ELSE FEB(*-4) - FEB(*-1); OVER
RECAP MAR(3,*,3) = IF MAR(*-1) EQ 0 OR MAR(*-4) EQ 0 THEN 0 ELSE MAR(*-4) - MAR(*-1); OVER
BAR AS "----" OVER
RECAP TOTS/D8c = APR + MAY + JUN + JUL + AUG + SEP + OCT + NOV + DEC + JAN + FEB + MAR; AS 'Totals'
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
grid=off, size=10, \$
type=acrosstitle, justify=center, \$
ENDSTYLE
END```

 In FOCUSsince 1986 WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 WebFOCUS App Studio 8.2.06 standalone on Windows 10

 Posts: 5681 | Location: United Kingdom | Registered: April 08, 2004 IP
Silver Member
 posted September 16, 2014 01:35 PM Hide Post
Interesting! This language seems quite powerful, I am reading some of the manuals right now.

Thank you for the code...it works with the sample data but when I try to use it on mine on I get the error:
WORD OR SYNTAX IN 'FOR' PHRASE (FML OPTION) NOT RECOGNIZED

I noticed in the sample code, you make a new define:
FISCAL_MONTH_OF_YEAR_NAME/Mt = DATE;
Year/YY = DATE;

I was wondering what this is for? As the FOR is referencing FISCAL_MONTH_OF_YEAR_NAME, I thought it would be okay to reference my joined table's field J0.DIM_DATE.FISCAL_MONTH_OF_YEAR_NAME

8.0.0.2
Windows, All Outputs

 Posts: 41 | Registered: February 27, 2014 IP
Expert
 posted September 16, 2014 03:04 PM Hide Post
The define of Year was residual code when I was validating how best to split the data within the sample data to try and represent something akin to your data. You can disregard that define.

The rest of the code I tried to keep as similar to your code, especially around the FOR syntax so that you should be able to derive most of what you would require from it.

Good luck

T

 In FOCUSsince 1986 WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 WebFOCUS App Studio 8.2.06 standalone on Windows 10

 Posts: 5681 | Location: United Kingdom | Registered: April 08, 2004 IP
Master
 posted September 17, 2014 10:00 AM Hide Post
For forecasting WF has a number of statistical routines but you need to be knowledgeable about statistics to a certain extent in order to interpret the results correctly.

Look up Linear Regression Forecast or Logistical Regression in Help

WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP

 Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010 IP
Silver Member
 posted September 17, 2014 01:52 PM Hide Post
Thanks!

Let me know if I am understanding this correctly, using the first line as an example:

RECAP APR(3,*,3) = IF APR(*-1) EQ 0 OR APR(*-4) EQ 0 THEN 0 ELSE APR(*-4) - APR(*-1); OVER

You are creating a calculation (RECAP) for a labelled rows (in this case APR) based on a couple of columns in the report. The (3,*,3) refers to where the value goes? Start column 3, * refers to end of report, and the other 3 refers to the incremental value), so add 3 (columns?) for the next column to put the values in?

For the (*-1) and (*-4), I understand this as to the left of the base column by 1 and by 4 columns...but how do I know which one is my base column?

I think something I am understanding is wrong...because when I try to follow the guideline for my actual table/values, I get the error CONFLICTING FORMATS PREVENTS COLUMN TOTALS: COUNT FIELD then when I remove that column field it runs but they are all "zero" so I am obviously referencing the wrong cells.

8.0.0.2
Windows, All Outputs

 Posts: 41 | Registered: February 27, 2014 IP
Expert
 posted September 17, 2014 02:54 PM Hide Post
You are correct on the reference (3,*,3) and in the (*-1) and (*-4) the base column is the one that is current in the (3,*,3). Simple? Once you get your head around the offsets etc.

This can be easily created in the GUI of both Dev Studio and App Studio (I've checked both) and the code produced is almost identical to what I've posted above - only mine tends to be aligned because I like to be able to read it during and after so that I know what it's doing.

Of course, if I created it in the GUI then I should never need to open it up in the editor

T

 In FOCUSsince 1986 WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 WebFOCUS App Studio 8.2.06 standalone on Windows 10

 Posts: 5681 | Location: United Kingdom | Registered: April 08, 2004 IP
Silver Member
 posted September 17, 2014 03:18 PM Hide Post
Is there a guide/where do I access the GUI in Dev Studio?? I am using version 8.0.0.8.

8.0.0.2
Windows, All Outputs

 Posts: 41 | Registered: February 27, 2014 IP
Expert
 posted September 18, 2014 05:44 AM Hide Post
From the main IBI site, hover over "Services and Support", move down to "Technical Documentation Library" and click on "WebFOCUS Technical Library".

Search on "FML" and you should find a whole host of links on the subject.

There is a tutorial named "Creating a Financial Report Using the Financial Report Painter" that should help you.

Good luck

T

 In FOCUSsince 1986 WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 WebFOCUS App Studio 8.2.06 standalone on Windows 10

 Posts: 5681 | Location: United Kingdom | Registered: April 08, 2004 IP
Silver Member
 posted September 22, 2014 12:01 PM Hide Post
Hi,

Thanks I'm slowly starting to understand this now. A couple of things:

When I run the sample report (ggsales) I am able to run it fine...but I cannot open it in the report painter, I am getting an error of "Error parsing report". I was trying to see how you made the report in the Financial Report Painter.

The reason is, when I try to write the formula for the RECAP, I am trying to put the value in Column 2 "variance" but it also makes my column 1 a RECAP and no longer a "TAG" type. Should the "RECAP" be a "new row"?
As well, when I actually put in the code through the text file, I don't get an "error" but all my values are 0. I am assuming there is something wrong with which columns it is referencing which is why I am trying to build it through the painter.

I've attached a screenshot of what I have done as a reference:
http://i.imgur.com/RSC1wwD.jpg

8.0.0.2
Windows, All Outputs

 Posts: 41 | Registered: February 27, 2014 IP
Expert
 posted September 22, 2014 05:40 PM Hide Post
The error is because you have not specified a TO column (as per message). If you want this to go to the end then you need to use "*".

Also, you only have two summated columns so your step should be 2 (if you note, my example has 3 summated columns). The offset to column -4 should also be adjusted accordingly.

If you work through the example on the link that I supplied rather than my example you should be OK. Once you have that sussed then add your across column and your recaps for the variance.

T

This message has been edited. Last edited by: Tony A,

 In FOCUSsince 1986 WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 WebFOCUS App Studio 8.2.06 standalone on Windows 10

 Posts: 5681 | Location: United Kingdom | Registered: April 08, 2004 IP
Member
 posted September 22, 2014 06:27 PM Hide Post
Please understand that more columns may satisfy relative notation than you might think. Non-printed columns may end up in the internal matrix due to possible references in computes, noprint fields. To control this, please see the documentation for:
SET CNOTATION={ALL|PRINTONLY|EXPLICIT}

WebFOCUS 7.6
Windows, All Outputs

 Posts: 24 | Registered: July 01, 2014 IP