Line chart help please

[#1]

FileID	Propose	UnderDev
1	3/11/2016	3/28/2016
2	3/25/2016	4/1/2016
3	4/1/2016	4/15/2016

I need to create line chart with 2 line which show count of each dates by Month.

In march Propose count is 2 and In Apr 1

In march Underdev count is 1 and In Apr 2

I may have to combine these 2 dates ? I am not sure.Can you help me ?Thank you.

[swuehl]

You could create a canonical date out of these two date fields:

Canonical Date

Then link the canonical date to a master calendar, and use the master cal. month as dimension.

Second dimension could be your date type field and expression =Count(Type)

[#3]

sunindia‌ Do yoy have any solution for this please ?

[#4]

Thank you and tried this approach but didnt work. I am still a beginner here !

[settu_periasamy]

Hi,

As suggested by swehul, you can try Canonical Date  with link of your master calendar.

still, If you want the solution based on your provided data, may be the below script will helps (this is not the good way)

T1:
LOAD * INLINE [
    FileID, Propose, UnderDev
    1, 3/11/2016, 3/28/2016
    2, 3/25/2016, 4/1/2016
    3, 4/1/2016, 4/15/2016
];
T2:
CrossTable(Type,Date)
LOAD * Resident T1;
New:
LOAD *,Month(Date) as Month Resident T2;
DROP Table T1,T2;

[sunny_talwar]

Canonical Calendar method:

Table:

LOAD * Inline [

FileID, Propose, UnderDev

1, 3/11/2016, 3/28/2016

2, 3/25/2016, 4/1/2016

3, 4/1/2016, 4/15/2016

];

LinkTable:

LOAD FileID,

  Propose as Date,

  'Propose' as Flag

Resident Table;

Concatenate(LinkTable)

LOAD FileID,

  UnderDev as Date,

  'UnderDev' as Flag

Resident Table;

Temp:

LOAD Max(Date) as MaxDate,

  Min(Date) as MinDate

Resident LinkTable;

LET varMaxDate = Peek('MaxDate');

LET varMinDate = Peek('MinDate');

DROP Table Temp;

Calendar:

LOAD Date,

  Month(Date) as Month,

  Year(Date) as Year,

  MonthName(Date) as MonthYear;

LOAD Date($(varMinDate) + IterNo() - 1) as Date 

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Dimension: Month

Expressions:

1) Count({<Flag = {'Propose'}>}Flag)

2) Count({<Flag = {'UnderDev'}>}Flag)

[sunny_talwar · Accepted solution]

Actually what Stefan mentioned works better I guess

Dimensions:

Month

Flag

Expression: Count(Flag)

[#8]

Thank you as always You are my role model