topic Re: Line chart with multiple dimensions from different tables in QlikView

Line chart with multiple dimensions from different tables

Mon, 21 Oct 2013 09:15:48 GMT

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

Re: Line chart with multiple dimensions from different tables

Gysbert_Wassenaar — Mon, 21 Oct 2013 09:53:31 GMT

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.

Re: Line chart with multiple dimensions from different tables

Mon, 21 Oct 2013 10:07:01 GMT

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