Bar chart of entries that are active on date

[#1]

Hi all, I'm trying to figure out how to do this and I don't seem to be getting very far with my Google searches.

What I am trying to achieve is I have a table of entries with a ValidFrom and a ValidTo Date field, which specifies the period that an entry is considered to be current for. I have been able to produce a bar chart of how many entries have the ValidFrom on each date but I would like for the count to be of how many entries were considered to be active on this date. I'm unsure of what I should be setting the dimension/expression to be.

Any guidance, especially recommended reading around this, is greatly welcomed.

[Gysbert_Wassenaar · Accepted solution]

This blog post may help: Creating Reference Dates for Intervals

[#3]

That looks perfect thank you. I get the feeling this is me doing something but when I edit the script I put in the following;

objs:
 LOAD
     objectstateid,
     validfrom,
     validto
 FROM object_state;
 
 object_x_dates:
 LOAD
     objectstateid,
     Date(validfrom + IterNo() - 1) as RefDate
     Resident objs
     While IterNo() < = (validto - validfrom + 1);

but when I 'Reload' the data I get the following error message;

Cannot open file 'D:\Documents and Settings\...\object_state' The system cannot find the file specified
 
 objs:
 LOAD
     objectstateid,
     validfrom,
     validto
 FROM object_state

I am supposed to be putting this code somewhere other than the script or am I doing something else completely wrong?

Thanks

[saumyashah90]

You havent given the path of the file where it is placed
you need to give it with its extension

[#5]

It's a table that has already been loaded into the application rather than a file

[saumyashah90]

if it has been already loaded then you should youse

Resident object_state

[#7]

Brilliant thank you for that