Aggregate on record which are valid in a period of time

[Edoedoedo]

Hi,

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.

Regards

[Sam_SAS]

Hello,

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,

Sam

[Edoedoedo]

Thank you for your answer, but unfortunately I'm not allowed to do that by the admin, all I can do is "Create Report" section and use this table as is...