topic Re: How to get value from previous date? in SAS Visual Analytics

How to get value from previous date?

ksulistyo — Thu, 30 Jun 2016 12:51:42 GMT

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

Re: How to get value from previous date?

LinusH — Thu, 30 Jun 2016 13:06:39 GMT

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.

Re: How to get value from previous date?

ksulistyo — Thu, 30 Jun 2016 13:25:28 GMT

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.

Re: How to get value from previous date?

Kurt_Bremser — Thu, 30 Jun 2016 13:33:25 GMT

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

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

Re: How to get value from previous date?

LinusH — Thu, 30 Jun 2016 14:14:28 GMT

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.

Re: How to get value from previous date?

ksulistyo — Wed, 13 Jul 2016 01:08:19 GMT

Is it possible to use Periodic Operators in SAS VA?

I haven't tried it yet.