Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am trying to create a line chart showing the number of active objects over a period of time. I have three fact tables describing different objects, each of which have a valid from and valid to dates defining the objects lifespan.
To create a line chart showing the number of objects that are active from my valid from/to dates, during the load I create a table with the unique identifier for the object and a date entry for every date in between the valid from and valid to date.
t_task_active_dates:
load
IterNo() as ID,
key_task_identifier,
Date(t_task.ValidFrom + IterNo() - 1) as ActiveDate
Resident t_task
While IterNo() <= (t_task.ValidTo - t_task_ValidFrom + 1);
To create the line chart I would then use the ActiveDate field as the Dimension and Count(t_task_active_dates.ID) as the Expression, which gives me a line chart with the correct values. I start having problems when I try to include the number of active objects for the other two fact tables. I can produce the tables exactly the same way, but if I add another Dimension and Expression for the next object then the values that are displayed on the chart are wrong.
I was wondering whether anyone had any suggestions on how I may be able to show these three lines on the same chart while still getting the correct values.
Thanks for any help/advice in advance.
Sam
You can try creating three charts and put them on top of each other. Or you can try concatenating your fact tables. Or you can create a data island that contains only the object id's and the dates and make sure that table is not linked to any other table.
Hi Sam,
If you have a field identifying each of the 3 different objects,
then you can add 3 expressions:
-if(id=1,count(records))
-if(id=2,count(records))
if(id=3,count(records))
where records would be your primary key.
with date as dimension.
This would give you 3 different trend lines on the same chart.
Regards,
Anju