03-30-2016 09:02 AM - edited 03-30-2016 09:07 AM
I'm getting stuck with this issue: I have a dataset which looks like this:
START_DATE | END_DATE | ID | VALUE
01/01/2016 | 31/12/9999 | 1 | 300
01/01/2016 | 02/01/2016 | 2 | 500
02/01/2016 | 31/12/9999 | 2 | 100
It is an historical table. It means that:
- ID #1 had a value of 300 on 01/01/2016, and it has still this value today
- ID #2 had a value of 500 on 01/01/2016, but then on 02/01/2016 it changed its value to 100, and it has still this value today.
So briefly the table says that each row is valid from START_DATE (included) until END_DATE (excluded).
I want to create a report in VA, where I show a simple line graph where, for each day, I aggregate VALUE only for the records that are valid in that day. In this example, the line graph should have:
- on X axis: 01/01/2016 and 02/01/2016
- on Y axis: 800 and 400
Why? Because on 01/01/2016 ID #1 has value 300 and ID #2 has value 500 so 800, and on 02/01/2016 ID #1 has value 300 and ID #2 has value 100 so 400.
How could I achieve this in VA? I must use directly this table (and of course any calculated variable or parameter) but I cannot rework the table to get it nicer.
Thanks a lot for any help.
03-30-2016 09:17 AM
You will probably want use some DATA step processing in the query for your data set. You can edit the SAS code for your data query in the visual data builder (access "Data Preparation" from the VA home page.)
Hopefully that will help,
03-30-2016 09:20 AM