topic Re: Multi Dates in one record, stacked bars in QlikView

Multi Dates in one record, stacked bars

sskinner — Thu, 28 Jul 2016 18:36:46 GMT

Hi, I have data which looks like this...

item-id, created-date, resolved-date
b434,2016-05-05, 2016-06-06
b435,2016-05-06,
b425,2016-06-06, 2016-06-06

How can I create a bar graph with the month as the y-axis, count of created-date as 1 bar, count of resolved-date as second bar? I can create two independent charts easily enough but do not know how to put them together. Need something like this to chart...

month, count-created, count-resolved

05, 2, 0

06, 1, 2

As searching the discussions, I created a 'calendar' table with months in it to see if that would help things (using that as the dimension) but no luck. Thanks!

Re: Multi Dates in one record, stacked bars

swuehl — Thu, 28 Jul 2016 19:30:18 GMT

Create a

Canonical Date

Re: Multi Dates in one record, stacked bars

sskinner — Thu, 28 Jul 2016 19:44:36 GMT

Ah, very good (and fast) response. I feared it would be a little complicated like that but will try it out. Thanks!

Re: Multi Dates in one record, stacked bars

flores_mig — Thu, 28 Jul 2016 21:56:56 GMT

Hi Stephen.

I do something that hope help.

//------------------------- Load Original Data

Table1:

LOAD * INLINE [

item-id, created-date, resolved-date,

b434, 2016-05-05, 2016-06-06,

b435, 2016-05-06, ,

b425, 2016-06-06, 2016-06-06

];

//------------------------- Transform to work with dates

Table2:

LOAD

[item-id],

[created-date],

[resolved-date],

Month([created-date]) as month_created,

Month([resolved-date]) as month_resolved

Resident Table1;

//-------------------------

Drop Table Table1;

//------------------------- Create a field to use as counter

Couter_cases:

LOAD Distinct

[item-id],

month_created as month,

1 as case_created

Resident Table2

where not IsNull(month_created);

JOIN

LOAD Distinct

[item-id],

month_resolved as month,

1 as case_resolved

Resident Table2

where not IsNull(month_resolved); !

Regards!

Mike.

Re: Multi Dates in one record, stacked bars

sskinner — Fri, 29 Jul 2016 13:04:01 GMT

Hi Miguel, I was able to get the first solution working before I saw this post. Thank-you though for it, may help me (others) with future challenges.