Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
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
Partner

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.