topic Re: Line chart help please in QlikView

Line chart help please

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Line chart help please

swuehl — Tue, 22 Mar 2016 21:40:05 GMT

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)

Re: Line chart help please

Wed, 23 Mar 2016 03:15:02 GMT

sunindia‌ Do yoy have any solution for this please ?

Re: Line chart help please

Wed, 23 Mar 2016 03:17:25 GMT

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

Re: Line chart help please

settu_periasamy — Wed, 23 Mar 2016 03:44:48 GMT

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;

Re: Line chart help please

sunny_talwar — Wed, 23 Mar 2016 09:01:39 GMT

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)

Re: Line chart help please

sunny_talwar — Wed, 23 Mar 2016 09:03:51 GMT

Actually what Stefan mentioned works better I guess

Dimensions:

Month

Flag

Expression: Count(Flag)

Re: Line chart help please

Wed, 23 Mar 2016 12:01:28 GMT

Thank you as always You are my role model