How to get value from previous date?

[ksulistyo]

Hello,

Is there any way in SAS VA to get value from the previous date (one day before), for example from this table:

--------------------------------------------------------
|    Date   | Company Name | Trx Value | Trx Value D-1 |
--------------------------------------------------------
| 01Jun2016 | ABC          |    10,000 |               |
| 01Jun2016 | DEF          |    11,000 |               |
| 01Jun2016 | XYZ          |    12,000 |               |
| 02Jun2016 | ABC          |    33,000 |        10,000 |
| 02Jun2016 | DEF          |    44,000 |        11,000 |
| 02Jun2016 | XYZ          |    55,000 |        12,000 |
| 03Jun2016 | ABC          |    60,000 |        33,000 |
| 03Jun2016 | DEF          |    70,000 |        44,000 |
| 03Jun2016 | XYZ          |    80,000 |        55,000 |
--------------------------------------------------------

The Trx Value D-1 is New Calculated Item that I would like to get.

Regards,

Karina

[LinusH]

Assuming that you mean by VA already loaded data to LASR.

I haven't worked in depth within VA, but since VA is a kind of standardized reporting tool, it works best with normalized/de-normalized data structures.

My guess is you need to fix calculations like this in the data preparation.

[ksulistyo]

Hi Linus,

Thanks for your response.

Ok, the calculation is done in Data Preparation/Data Builder by adding new column, how does the SQL expression or the logic should look like (I attach the form)? I'm bit frustrated here 😕 

Kindly enlighten.

Thanks.

[Kurt_Bremser]

Like @LinusH said, put that into the date before loading into VA.

Sort by company and date and use the lag() function.

[LinusH]

SQL is not aware of row ordering, so any lag() or RETAIN operations isn't available here.
If you are limited to Data Builder you should be able to pull this off by doing a self join with a date = date -1 criteria.

[ksulistyo]

Is it possible to use Periodic Operators in SAS VA?

I haven't tried it yet.