Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
Not applicable

line chart with missing data point

Hi!

If you look at the attached file, you will see the line chart.

please take a closer look at the line that is running across relatively horizontally across the chart. the apricot color.

the thing about this line is that it has only three data points

two at the beginning of all dates and one at the very last date on the chart.

what this causes is even though the mid date range does not have any data for the apricot line, because it has data points at the beginning and at the end, the line goes across the chart. I want the line to go down to zero when the data point does not exist.

Please advise!!

Thank you in advance!

David

Tags (1)
1 Solution

Accepted Solutions

Re: line chart with missing data point

May be some manipulation in the script:

Table:

LOAD Date as TimeStamp,

  Date(Floor(Date)) as Date,

    Name,

    [Project Name],

    Count,

    Hours

FROM

[https://community.qlik.com/thread/235224]

(html, codepage is 1252, embedded labels, table is @1);

TempTable:

Left Keep (Table)

LOAD Date(Min + IterNo() - 1) as Date

While Min + IterNo() - 1 <= Max;

LOAD Min(Date) as Min,

  Max(Date) as Max

Resident Table;

Left Join (TempTable)

LOAD [Project Name]

Resident Table;

Capture.PNG

17 Replies

Re: line chart with missing data point

What is the expression that you are using for that line? Can you share it?

Not applicable

Re: line chart with missing data point

Sum( {<[Project Name] = {'Project1'}>} [Hours]) /  Sum([Hours])

Hi Sunny!

above is the expression that I am using.

and I am also using master calendar so if I get all the data in table chart, the dates that does not have any data still shows up with all the other column simply - - - - .

let me know if you need any other info.!

Re: line chart with missing data point

Hey David -

Can you see if this works:

Sum({<[Project Name] = {'Project1'}>} [Hours]) /  Sum([Hours]) + Avg({1} 0)

If this doesn't, would you be able to share a sample to look at?

Re: line chart with missing data point

Just for my curiosity, try this as well:

Sum({<[Project Name] = {'Project1'}>} [Hours]) /  Sum([Hours]) + Avg(TOTAL {1} 0)

Not applicable

Re: line chart with missing data point

Hi Sunny

I tried both but nothing happened.

    

    

DateNameProject NameCountHours
9/21/2016 7:12AmyProject A927
9/21/2016 7:43TanyaProject C81.5
9/22/2016 7:50TanyaProject C61
9/23/2016 7:24AmyProject B12243
9/26/2016 7:21AmyProject B21367
9/23/2016 7:23HannahProject B4061.5
9/26/2016 7:28HannahProject B21986
9/26/2016 7:59TanyaProject B10002.5
9/27/2016 7:09AmyProject B24546.5
9/27/2016 7:40HannahProject B27206
9/27/2016 7:53TanyaProject B4351
9/27/2016 7:54TanyaProject C41
9/29/2016 6:55AmyProject B29366.5
9/28/2016 7:07IhmProject B30878
9/28/2016 7:27DavidProject B27828
9/28/2016 7:10PatrickProject B30318
9/28/2016 7:50HannahProject B25046
9/28/2016 7:56TanyaProject C71.5
9/28/2016 7:57TanyaProject B10343
9/29/2016 7:24IhmProject B28366.5
9/29/2016 7:25AmyProject B24986
9/29/2016 7:38DavidProject B27857
9/29/2016 7:41PatrickProject B30257
9/29/2016 7:48HannahProject B21045
9/29/2016 7:34TolgaProject B14005
10/1/2016 6:49PatrickProject B30438
9/30/2016 7:03AmyProject B27567.5
9/30/2016 7:18IhmProject B31588
9/30/2016 7:37TolgaProject B10014
9/30/2016 7:31DavidProject B30348
9/30/2016 7:47TanyaProject C20.5
9/30/2016 7:47TanyaProject B14004.5
10/3/2016 7:33PatrickProject B30508
10/3/2016 7:35IhmProject B30448
10/3/2016 7:46TanyaProject C112
10/4/2016 7:32HannahProject B17414
10/4/2016 7:49TanyaProject C10.5
10/4/2016 7:50IhmProject A3008
10/4/2016 7:46PatrickProject A3106

Okay. let's say this is an example.

as you can see Project A has big chunk of time in between.1Capture.PNG

This is the original chart. this chart has more data than what i have provided, but above provided data will be enough for you to chart this out. the apricot color straight line is the line for project A. and as you can see since it only has data at the end of the dates line becomes straight.

Hope the dataset helps you explain this problem

Re: line chart with missing data point

May be some manipulation in the script:

Table:

LOAD Date as TimeStamp,

  Date(Floor(Date)) as Date,

    Name,

    [Project Name],

    Count,

    Hours

FROM

[https://community.qlik.com/thread/235224]

(html, codepage is 1252, embedded labels, table is @1);

TempTable:

Left Keep (Table)

LOAD Date(Min + IterNo() - 1) as Date

While Min + IterNo() - 1 <= Max;

LOAD Min(Date) as Min,

  Max(Date) as Max

Resident Table;

Left Join (TempTable)

LOAD [Project Name]

Resident Table;

Capture.PNG

Not applicable

Re: line chart with missing data point

Hi Sunny,

I have made changes according to what you have given me but unfortunately nothing change

but I really really want to understand what you were trying to do here.

could you explain what this is doing?

1. why did you add

LOAD Date as TimeStamp,



2. and all the codes that are in the last half...



I really want to make it work like it is working on your side.

Thanks in advance

David

Not applicable

Re: line chart with missing data point

esp. left keep / resident / IterNo.

Re: line chart with missing data point

1)  Since date included time, I wanted to create a field which was truly just date

Date as TimeStamp,

Date(Floor(Date)) as Date,

2) All the part in red is to create a table which would include all possible combination of dates and Project Names. The problem with your original data is that not all Project Names have all the dates, right? And they are not just nulls, they are completely missing. So by the means of the code in red above, I recreated a table which included all possible combinations of Project Names and dates.

Does that make sense?