topic Re: How to combine multiple date dimensions in a chart? in QlikView

How to combine multiple date dimensions in a chart?

Fri, 20 Feb 2015 16:29:17 GMT

I am relatively new to Qlikview, so apologies if this has been asked before, or is fairly straightforward.

I have some help desk type data that comes from one table. Each help desk ticket has an Opened Date and Closed Date, in addition to other attributes.

Essentially I am looking to create a line chart with MM/YY across the x-axis and the # of tickets as the measure.

Where my difficulty comes in is we are looking to do a count of both Opened and Closed Tickets for each month. It is possible for tickets to be opened one month and closed in the same month, or opened in one month and closed in a later month so I cannot used opened date (or closed date) as the dimension. I am sure I have to some how create a master calendar and join it to both opened and closed month, but I am still a little unclear on how to use just a date dimension and include both # of opened and # of closed tickets for each month).

Hopefully the description is clear enough. At its root I want to combine both the of the charts in the attached image into one chart, with an independent date dimension. ie you can see Feb has opened tickets, but not closed tickets, whereas Jan has closed tickets, but no opened tickets. I would want both of these months included.

I am not tied to a line chart necessarily, so if I need to use a bar chart or a combo chart it likely could work too.

We just want to show the # of tickets opened vs closed for each month consolidated on one chart. And please don't judge the simplistic formatting, I just threw these together quickly for demonstration purposes

Any suggestions are greatly appreciated!

Re: How to combine multiple date dimensions in a chart?

fashid — Wed, 25 Feb 2015 09:48:05 GMT

Hi,

you might be having some identifier or a flag field for closed ticket and open ticket in your tables. You can use that count against a common date field .

The date field as you already told would be from a master calendar.

see this discussion One table, Two Date Fields

Regards,

Nadeem

Re: How to combine multiple date dimensions in a chart?

Anonymous — Wed, 25 Feb 2015 10:42:26 GMT

Hi Karina,

Count tickets based on UniqueID irrespective of status.

Regards

Neetha

Re: How to combine multiple date dimensions in a chart?

Anonymous — Wed, 25 Feb 2015 10:45:18 GMT

Hi,

Try:

In a Straight/Pivot table:

dimension = Month
expression = count(distinct Ticket)

Regards

Neetha

Re: How to combine multiple date dimensions in a chart?

neha_sri — Wed, 25 Feb 2015 11:00:32 GMT

Hi Karina,

May be you can work something as you can autogenerate all 12 months and count(closed tickets) and give this line some rgb() and another you can count(open ticket) and give rgb() to it also. In this way you can compare your open vs. closed tickets.

Re: How to combine multiple date dimensions in a chart?

Wed, 25 Feb 2015 15:32:04 GMT

I think you can create independent date field for the years you are working on.i dont see any reason to use the dates present in the data to create a date dimension as that data is not continuous and would show only months that are present in the data

Re: How to combine multiple date dimensions in a chart?

agni_gold — Thu, 26 Feb 2015 18:05:46 GMT

I think this might be a good solution.