April Steele Posted March 31, 2023 Share Posted March 31, 2023 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? Link to comment Share on other sites More sharing options...
David Briars Posted March 31, 2023 Share Posted March 31, 2023 -* 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 Link to comment Share on other sites More sharing options...
David Beagan Posted April 1, 2023 Share Posted April 1, 2023 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 YEARto that expression. Link to comment Share on other sites More sharing options...
Fredrik Rosell 3 Posted April 3, 2023 Share Posted April 3, 2023 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now