Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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.