topic Re: Visualize time series in QlikView

Visualize time series

Sat, 17 Dec 2011 13:30:41 GMT

I would like to create a time series chart in a new tab which is semi-dependent on drill-down in a previous chart. The previous tab drills-down as Category>Subcategory>Object.

In the dataset I also have Object ID.

The previous tab shows the top 10 Objects after drill down for a restricted point in time. I would like to display the WHOLE time series for those 10 objects (based on x axis time (Date) and Object Value, with the Objects appearing in the legend.

How might I do this?

Thanks.

Visualize time series

swuehl — Sat, 17 Dec 2011 13:51:05 GMT

After your drill down in your first tab, do you have actually selected those top 10 Objects or do you just use e.g. a sorting and presentation limitation in the object properties?

Or could you specify, on which fields you have selected on so far (e.g. by drilling down or selecting in list box)?

Visualize time series

Sat, 17 Dec 2011 14:41:42 GMT

Just using presentation limitation in the object properties, though I may drill down to 1 object only (in which case i'd like my time series chart to show values only in relation to that 1 object.

I'll be navigating via drill down. So i guess if i haven't drill-down to the object level, it should show all the objects for the subcategory or category (depending on what level of the drill down navigation i'm at)...

Visualize time series

swuehl — Sat, 17 Dec 2011 22:59:14 GMT

Ok, I think you should try this:

Create a line chart with 2 dimensions, Date and Object, and one expression:

=sum({$<Date=, Object= {"=rank(sum(ObjectValue))<=10"} >} ObjectValue)

I use a set expression here, if you are not familiar with this, please have a look at the Help - set analysis.

The set expression kind of uses a selection only valid for the expression, and I use the current selection ($ set identifier), then clear any selection in Date field (I assume that you restricted Date in the other tab. You would need to clear all date fields you have selected in, so maybe add year, month etc. if needed.

Clearing the selection on Date will lead to your diagram showing the complete range of dates (whole time series).

Then I set a new Selection in field Object, and I use a search to retrieve the Objects I want, I am looking for the Objects with rank <=10 based on sum(ObjectValue), so I want the Top10).

Hope this helps,

Stefan

Re: Visualize time series

Sat, 17 Dec 2011 23:22:48 GMT

Thanks, but I'm afraid I can't deselect dates in other tabs as its time series data-naturally restricts as I drill down. Is there anyway to set it up via the script maybe?

Visualize time series

swuehl — Sat, 17 Dec 2011 23:48:18 GMT

No, you don't need to deselect anything in the other tab. The set expression only affects the expression which it is located in (the one in the line chart). No other objects will see the modified set.

Just give it a try...

Re: Visualize time series

Sun, 18 Dec 2011 00:21:40 GMT

Ok, just tried.. I'm afraid it only plots as per the date restrictions on the previous tab..

Re: Visualize time series

swuehl — Sun, 18 Dec 2011 00:29:32 GMT

Have you cleared all fields you restrict your time with in the set expression, with exactely the field names you are using?

I have attached a simple sample file that hopefully will show you how this works.

Re: Visualize time series

Sun, 18 Dec 2011 00:59:17 GMT

Yes, all fields in your expression replaced with my actual field names. I still keep getting little dots, as well as longer but still limited time series on the graph.

My drill down in the other tab, for example, is looking to isolate the top x objects for which the current value is greater than the previous value by x percent. So in this case, the tolerance is exceeded, for example, once during the life of the series - hence the dots (as they seem to correspond)?

Visualize time series

swuehl — Sun, 18 Dec 2011 01:07:55 GMT

Sorry, I don't understand what you mean with little dots.

Could you upload a small sample? With your data or my sample modified by you to match your setting?

Re: Visualize time series

Sun, 18 Dec 2011 12:04:24 GMT

PFA test document. If you drill down using the chart above to the object level, you'll see that the time series chart only shows values for the specific dates drilled down to.....

Please note this will not be my only drill down tab, I have other tabs as well that will drill down to the object based on different criteria. It will work in a similar way though.

Re: Visualize time series

swuehl — Sun, 18 Dec 2011 22:31:48 GMT

Ok, I think I understand what you mean with small dots.

That's due to the fact that your data does not show data for all dates (on a object level). To overcome this, I think there might be several approaches. You could play around with some options in dimension tab and presentation tab (show all values, suppress NULL, de-select suppress zero values etc.). I tried to get along with this in the lower left line chart, see attached.

You could also try to fill the missing data for the x-axis, either by creating a Master calendar that is linked to your data or by creating a data island. I played around and found the best looking results with an data island (lower right chart), but just play around yourself.

I am not sure if this fits all your requirement especially regarding your drill down with different charts in different tabs, but I hope we are moving on.

Hope this helps,

Stefan

Visualize time series

Sun, 18 Dec 2011 23:11:47 GMT

Hi, thanks, but yes I'm afraid the drill-down capability and transition to full time series data is integral to the document.

I have actually tried to play with the options but I seem to come back to the same problem. Instead of filling the missing data (I have too muc and it would be misleading according to what i'm doing), perhaps I can try reloading the data in an independent table and linking on the object ID? Would that work? But it wouldn't be optimal in any case as we're talking about millions of rows of data.

Visualize time series

swuehl — Sun, 18 Dec 2011 23:50:46 GMT

Not sure what you mean with the independent table, could comment on your idea here (what is the content of this table)?. Have you tried with the data island for the Date like in my example?

It's a bit hard to give you any advice since I don't know your complete data model nor your requirements.

Re: Visualize time series

Mon, 19 Dec 2011 00:00:34 GMT

Effectively the same data that I've already loaded in (Object ID, Object, Date, Value, etc), but loading it again as a different table and then just joining on the Object IDs. So my time series tab would read from the second table. Again, if this could be done, I really don't think it would be a good idea due to the volume of data involved.

Yes, I've had a crack with your example...

The data model is quite simple, effectively as shown in the previous upload. The various tabs look at different things, but the behaviour is the same. I'm seeking to drill through several categories and subcategories to identify objects, according to different tolerance levels, which may me considered as issues. Naturally, the issues would be restricted to certain points in time and spread throughout the life of the series.

Date: Sun, 18 Dec 2011 18:51:02 -0500

From: qliktech@sgaur.hosted.jivesoftware.com

To: aminul_ali@hotmail.com

Subject: - Re: Visualize time series

QlikCommunity

Re: Visualize time series created by swuehl in Development (QlikView Desktop) - View the full discussion

Not sure what you mean with the independent table, could comment on your idea here (what is the content of this table)?. Have you tried with the data island for the Date like in my example?

It's a bit hard to give you any advice since I don't know your complete data model nor your requirements.

Reply to this message by replying to this email -or- go to the message on QlikCommunity

Start a new discussion in Development (QlikView Desktop) by email or at QlikCommunity

Announcement: Join us for our first "Ask the Expert" event this week! The QlikView 11 Ask the Expert will be held on December 15-16th on QlikCommunity. More info »

Visualize time series

swuehl — Mon, 19 Dec 2011 00:20:15 GMT

I don't think just duplicating the table and linking by ObjectID (so all other fields are renamed?) will help here, but maybe I just don't understand your idea right.

If you had a look at my example, at which point do you think it will not work? If your various tabs look at different things, you mean you will always drill down to object level? The normal QV behaviour will be, that all selections in the different tabs will be taken and used to filter the data. Or do you want to filter independent in each tab? This is a complete different task then.

I am sorry, if my examples are not much of a help, I think I can't be of any further assistance since it seems I don't really understand what your are after. Maybe I should step back and leave it to someone who understand your requirements better.

Regards,

Stefan