Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sskinner
Contributor II
Contributor II

Multi Dates in one record, stacked bars

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!

4 Replies
swuehl
MVP
MVP

Create a

Canonical Date

sskinner
Contributor II
Contributor II
Author

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

flores_mig
Partner - Contributor II
Partner - Contributor II

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);cases.PNG !

Regards!

Mike.

sskinner
Contributor II
Contributor II
Author

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.