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
PROD_FACT_3M_DAD_IP_WEIGHTED_CASES.FACT_3M_DAD_IP_WEIGHTED_CASES.DISCHARGE_DATE
IN PROD_FACT_3M_DAD_IP_WEIGHTED_CASES TO MULTIPLE DIM_DATE.DIM_DATE.DATE
IN DIM_DATE TAG J0 AS J0
END
DEFINE FILE PROD_FACT_3M_DAD_IP_WEIGHTED_CASES
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
IF PROD_FACT_3M_DAD_IP_WEIGHTED_CASES.FACT_3M_DAD_IP_WEIGHTED_CASES.DISCHARGE_DATE LE '2015-04-01' THEN '2014-2015';
END
TABLE FILE PROD_FACT_3M_DAD_IP_WEIGHTED_CASES
SUM
CNT.PROD_FACT_3M_DAD_IP_WEIGHTED_CASES.FACT_3M_DAD_IP_WEIGHTED_CASES.ACCOUNT_NUMBER AS 'Cases'
PROD_FACT_3M_DAD_IP_WEIGHTED_CASES.FACT_3M_DAD_IP_WEIGHTED_CASES.RIW
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'
ACROSS LOWEST PROD_FACT_3M_DAD_IP_WEIGHTED_CASES.FACT_3M_DAD_IP_WEIGHTED_CASES.Fiscal_year

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

Have you thought about using FML?

Many posts abound in the forum.

T

Hi, Thanks, what is FML referring to?

FML : "Financial Modeling Language" module of IBI

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

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

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

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

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.

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

Is there a guide/where do I access the GUI in Dev Studio?? I am using version 8.0.0.8.

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

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

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,

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}