Line chart with multiple dimensions from different tables
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.
IterNo() as ID,
Date(t_task.ValidFrom + IterNo() - 1) as ActiveDate
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.
Re: Line chart with multiple dimensions from different tables
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.