Calculated Column (YTD) based on Month and Year

[April Steele]

Need to see if this is possible> I would like to do a calculated column where each month is added to get a YTD total by Month.

See attached example. Does anyone have a expression I can use or is this even possible?

[David Briars]

-* Create temporary Year and Month fields.DEFINE FILE GGSALES YEAR/YY = DATE; MONTH/M = DATE;END-* Extract data from Sales database and-* sort by Region/Year/Month & accumulate Dollars.TABLE FILE GGSALESSUM DOLLARSCOMPUTE CALC_COLUMN/D12 = IF REGION EQ LAST REGION THEN CALC_COLUMN + DOLLARS ELSE DOLLARS; BY REGIONBY YEAR BY MONTHWHERE YEAR EQ 1996WHERE MONTH LE 3ON TABLE HOLDEND-RUN-* Sort extract by Year/Month/Region. TABLE FILE HOLDPRINT DOLLARS/D12 CALC_COLUMNBY YEARBY MONTHBY REGIONON TABLE SET STYLE * INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/combine_templates/ENWarm.sty,$ENDSTYLEEND -EXIT

 

[David Beagan]

I think what you have written in line 9 of your code qualifies as an 'expression'. And it sure looks like you need a hold file, like you created, to get the final result in a sort order consistent with the provided example.

If the report needs to span multiple years, then could add

AND YEAR EQ LAST YEAR

to that expression.

[Fredrik Rosell 3]

Hello,

This question had both Spotfire and WebFOCUS topics set on it. April - please clarify what specific product you are asking about (or maybe it actually is about both).

There seems to be answers about WebFOCUS already, so here's an example of what could be done in Spotfire:

The calculated column I used for this:

Integer(Sum([Value]) OVER (Intersect([Department],[Year],AllPrevious([Date]))))

 

I'm not sure this is exactly what you had in mind, but the general methods - using Intersect to look at certain subsets of the data that "intersect" (so you can get values for a certain year and department), and AllPrevious to use all the nodes from the start of the level - is likely what you need.

You can find information about how OVER expressions work in Calculated Columns, including examples that are quite similar to what I used here and explain them in more detail, in the Analyst manual:

https://docs.tibco.com/pub/sfire-analyst/12.3.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/index.htm#t=ncfe%2Fncfe_over_in_calculated_columns.htm&rhsearch=calculated%20column&rhhlterm=calculated%20column&rhsyns=%20