Multi Dates in one record, stacked bars

[sskinner]

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!

[swuehl]

Create a

Canonical Date

[sskinner]

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

[flores_mig]

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.

[sskinner]

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.