Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

Line chart help please

FileIDProposeUnderDev
13/11/20163/28/2016
23/25/20164/1/2016
34/1/20164/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.

1 Solution

Accepted Solutions

Re: Line chart help please

Actually what Stefan mentioned works better I guess

Dimensions:

Month

Flag

Expression: Count(Flag)


Capture.PNG

View solution in original post

7 Replies
MVP
MVP

Re: Line chart help please

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)

Not applicable

Re: Line chart help please

sunindia‌ Do yoy have any solution for this please ?

Not applicable

Re: Line chart help please

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

Re: Line chart help please

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;

Capture.JPG

Re: Line chart help please

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);


Capture.PNG


Dimension: Month

Expressions:

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

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

Re: Line chart help please

Actually what Stefan mentioned works better I guess

Dimensions:

Month

Flag

Expression: Count(Flag)


Capture.PNG

View solution in original post

Not applicable

Re: Line chart help please

Thank you as always You are my role model