Hello..Please forgive me if this is a ridiculous question, as I am still very new to this.

I need to use a figure from a prior date in a compute and compare with current.

Below is a rough example of what I am looking for. Please let me know if you can help or if I need to explain further.

 TABLE FILE GGSALES
PRINT
DATE
STCD
DOLLARS

BY DATE NOPRINT
BY STCD NOPRINT
ON TABLE HOLD AS SORT FORMAT FOCUS INDEX STCD DATE
END
-RUN

-*
-* HERE I WOULD LIKE TO PULL THE SALES FROM THE FIRST DATE FOR EACH STORE AS 
-* PRIOR_DATE_SALES FOR EACH DATE AND STCD THEN JOIN THE HOLD INTO A NEW TABLE AND COMPARE DOLLARS IN A COMPUTE
-* 

TABLE FILE GGSALES
SUM
STCD
DATE
DOLLARS
PRIOR_DATE_DOLLARS
COMPUTE DIFF/D8.2 = (DOLLARS - PRIOR_DATE_DOLLARS); 
BY STCD
END
-RUN 

This message has been edited. Last edited by: Maggie McBride,

This may require tweaking to suit your needs, but it hopefully is a start. What to do about the Prior Date Dollars for the first date for each store?

SET HOLDLIST=PRINTONLY
SET BYDISPLAY=ON

TABLE FILE GGSALES
SUM
DATE
STCD
DOLLARS/D12.2
BY STCD NOPRINT
BY DATE NOPRINT
ON TABLE HOLD AS HDOLLARS
END
-RUN

DEFINE FILE HDOLLARS
PRIOR_DATE_DOLLARS/D12.2 = IF STCD NE LAST STCD THEN 0 ELSE LAST DOLLARS;
END

-* for testing
TABLE FILE HDOLLARS
PRINT
DOLLARS
PRIOR_DATE_DOLLARS
COMPUTE DIFF/D12.2 = DOLLARS - PRIOR_DATE_DOLLARS;
BY STCD
BY DATE
END

-* report
TABLE FILE HDOLLARS
SUM
DOLLARS
PRIOR_DATE_DOLLARS
COMPUTE DIFF/D12.2 = DOLLARS - PRIOR_DATE_DOLLARS;
BY STCD
END

Documentation: Using Functions > Data Source and Decoding Functions > LAST: Retrieving the Preceding Value

Note that the data must be in the correct order for the LAST "function" to work as expected, hence the HOLD file before the report.

I'm thinking she's looking for the first date for each store compared to a later date, not the prior date.

 
TABLE FILE GGSALES
SUM MIN.DATE AS 'FIRST_DATE'
BY STCD
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS FIRSTDATE FORMAT FOCUS
END

JOIN STCD AND FIRST_DATE IN FIRSTDATE TAG FD TO STCD AND DATE IN GGSALES TAG GG AS J1 ;

TABLE FILE FIRSTDATE
PRINT FIRST_DATE
      DOLLARS AS 'FIRST_DATE_DOLLARS'
BY STCD
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS FIRSTDAYDOLLARS FORMAT FOCUS INDEX STCD
END

 

At this point you have your first day data for each store in a simple table. A simple JOIN on STCD from GGSALES to FIRSTDAYDOLLARS should attach your FIRST_DATE and FIRST_DATE_DOLLARS fields to all records in GGSALES.


For the record I ask far more ridiculous questions here, and often get ridiculous answers from users I shouldn't name. FRANCIS, what do you think?

Thank you Both. Francis hit the nail on the head with the solution I was looking for; It worked perfect.

I don't know what I would do without this forum! I have learned most that I know about WebFocus on here.

I appreciate the support! I am sure you will be hearing from me soon in another post!