2 Replies Latest reply: Oct 21, 2013 6:07 AM by Anju Gopinath RSS

    Line chart with multiple dimensions from different tables

      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