topic Graph using dates from two calendars issue in QlikView

Graph using dates from two calendars issue

Fri, 06 May 2011 10:09:56 GMT

Hi,

I’ve a bugtracker system and I want to extracthistorical information about tickets’ open/close.

First of all, I created a master calendar linkedto opendate and another linked to closedate.

The problem is that I don’t know how analyzing thedata using a graph chart. I set MonthName (refers to opendate calendar) asDimesion, Count(opendate) and Count(closedate) as expressions.

If ticket is opened in January and closed inFebruary, I’d like to see two columns (one –open- in Jan, second-close- inFeb).

Regards

Graph using dates from two calendars issue

erichshiino — Sat, 07 May 2011 21:03:57 GMT

The result you got is due to the use of the dimension related to ticket creation.

Maybe you could merge all the dates in one single calendar. and create on line for each 'Event'

For example,

Ticket:1

Date Opened: 1/mai/2011

Date Closed: 06/jun/2011

It will become

Ticket:1

Event: Opened

Date: 1/mai/2011

and

Ticket:1

Event: Closed

Date: 6/jun/2011

but it can change you data model a lot...

Graph using dates from two calendars issue

Mon, 09 May 2011 07:42:43 GMT

Hello Erich,

Thanks for the answer but I cant change the data model.

Anybody has a possible idea?

Regards

Graph using dates from two calendars issue

ToniKautto — Mon, 09 May 2011 09:29:51 GMT

Please attach a sample QVW to make analysing your issue a bit quicker. If it has sensitive data, please use the Scrambling function to make data unreadable.

Settings > Document Settings > Scrambling

Re: Graph using dates from two calendars issue

Mon, 09 May 2011 09:57:54 GMT

Hi Toni,

Thanks for your advice. I've just attached it.

Graph using dates from two calendars issue

ToniKautto — Mon, 09 May 2011 11:25:21 GMT

Unfortunately you can not accomplish such a graph, since your data in reality goes over two different dimensions by having two calendars. Only way to get around this is to have the data presented over the same dimensions, being one general calendar.

The quickest way around this is to do exactly as Erich proposed above. The reason for this is that by linking to fields from the datat table to the calender table will lead to a synthetic key. The data model will allow you to plot the way you want, bu thte result might not be fully trustable. By adding event rows as suggested you will on the other hand be in full control over your data model.

Not clear over why you can not alter the data model, but I am quite sure that the change is quite easy to implement and enable you to do what you want in a feasible way.

Re: Graph using dates from two calendars issue

Mon, 09 May 2011 12:39:09 GMT

Hi,

Probably you are looking for the attached example. I have taken the two dates from your tickets table to demonstrate it.

What I understand is you need to show the number of tickets closed at various months for the tickets opened in a particular month. Hope my understanding is correct.

Please have a look at the attached example and let me know. Attaching the Excel file with the dates and QVW file with the required graph.

Best Regards,

Sajeevan

TNS Dubai

Graph using dates from two calendars issue

ToniKautto — Mon, 09 May 2011 14:03:58 GMT

If you are going to rewrite the script any way, I really think it is a better structure to have a event focused fact table and link it to a calendar. This will give a much better overview from a designer perspective and better possibilities to analyse the data, by doing set analysis over dates and states.

Consider the possibilty to not just solve a specific current requirement, but also opening up for other analysis options in the future. It is much easier to make a proper data modelling from the begining, than to have to redo everything later on when you want to add addtional features to your application.

Graph using dates from two calendars issue

Mon, 09 May 2011 14:30:47 GMT

It all depends what you want to analyse and report.

Re: Graph using dates from two calendars issue

Tue, 10 May 2011 07:35:13 GMT

Hey,

Thanks for all your replies. Finally, I did it like Sajeevan said. Althought it'd be nice to do like Toni/Erick said (in near future).

Toni, Could you show me any example -code or qvw- of using your method?

Regards