Or this, if you need to reset the running total (and if the data is in the correct order for the report):
TABLE FILE CAR
SUM
SALES
COMPUTE SALES_RT/D6 = IF COUNTRY EQ LAST COUNTRY THEN SALES_RT + SALES ELSE SALES;
RETAIL_COST
COMPUTE RETAIL_COST_RT/D6 = IF COUNTRY EQ LAST COUNTRY THEN RETAIL_COST_RT + RETAIL_COST ELSE RETAIL_COST;
BY COUNTRY
BY CAR
END
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
September 25, 2014, 01:35 PM
ColdWhiteMilk
What if I need to reset the total not just on country, but to only include records in the total that have a date that fall within a specific range in relation to the date of the record?
For example: -the total for row 1 would have a total of all records that have a date value in the in the last 7 days from that date value (12/24/2013 to 01/01/2014). -the total for row 2 would have a total of all records that have a date value in the in the last 7 days from that date value (12/25/2013 to 01/02/2014).
Production - 7.6.4 Sandbox - 7.6.4
September 25, 2014, 01:49 PM
Francis Mariani
If you can translate "total of all records that have a date value in the in the last 7 days from that date value (12/24/2013 to 01/01/2014)" into an IF statement you should be able to put that in the COMPUTE.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
September 25, 2014, 02:38 PM
ColdWhiteMilk
something like:
COMPUTE TOT7= IF ID EQ LAST ID AND COUNTRY EQ LAST COUNTRY AND DATE-7 GT LAST DATE THEN SALES + TOT7 ELSE SALES;
but this just gives me a running total still for the whole data set, it does not re-set when the result is false.
Production - 7.6.4 Sandbox - 7.6.4
September 26, 2014, 10:27 AM
MartinY
Maybe you should move your COMPUTE into a DEFINE instead...
Because you're now testing based on a date, COMPUTE will use the last or first (can't remember which one is stored in the Matrix when not specified) date from your WHERE selection.
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