Jump to content

Calculated Column (YTD) based on Month and Year


April Steele

Recommended Posts

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

image.png.d022d537d7844209169f15fff11059f0.png 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

YTD.thumb.png.057bda0ca97087af42cd70094796ec2d.png 

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...