Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
stevelord
Valued Contributor

Line Graph, put 0s where no data present for activity date?

Hi, I have activity log data by day where people log activity on many days, but not all days.  On my line charts, if a person logs 5 on May 1, has no data present on May 2, and logs 5 on May 3, I end up with a line cutting straight across from May 1 to May 3 when I'd like it to V with a 0 on May 2.  The source data is created by an automation that only puts people on the list each day if they had activity that day- it doesn't populate with people who have 0 activity that day, so I can't just pull in 0s.

Can anyone shoot me some tried and true solution to make this happen?

Source data looks like

Date, Name, Count

May 1 2014, Joe, 5

May 1 2014, Bill, 3

May 2 2014, Bill, 2

May 3 2014, Joe 5

May 3 2014, Bill 1

And I want the graph to show 5,0,5 in a V shape for Joe instead of a flat line with no date under the middle.  (The date will be there if both Joe and Bill are present on the graph.)

Thanks!

Tags (3)
1 Solution

Accepted Solutions
nagaiank
Valued Contributor III

Re: Re: Line Graph, put 0s where no data present for activity date?

I have attached the sample application using my code.

Make sure vMinDate and vMaxDate has date as numbers.

If you are using formatted date string such as 05/01/2014, convert the dates to mumbers. For example

vMinDate = Num(Date#('05/01/2014','MM/DD/YYYY'));

vMaxDate = Num(Date#('05/03/2014','MM/DD/YYYY'));

Hope this helps.

5 Replies
nagaiank
Valued Contributor III

Re: Line Graph, put 0s where no data present for activity date?

For your sample data, use the following script to reach a solution.

Data:

LOAD Date(Date#(Date,'MMM D YYYY')) as Date1, * Inline [

Date, Name, Count

May 1 2014, Joe, 5

May 1 2014, Bill, 3

May 2 2014, Bill, 2

May 3 2014, Joe, 5

May 3 2014, Bill, 1

];


Temp:

LOAD Max(Date1) as maxdate, Min(Date1) as mindate Resident Data;

LET vMinDate = Peek('mindate',-1,'Temp');

LET vMaxDate = Peek('maxdate',-1,'Temp');

DROP Table Temp;


AllDates:

LOAD Date($(vMinDate) + IterNo() - 1) as RequiredDate

AutoGenerate 1

While $(vMinDate)+IterNo()-1 <= $(vMaxDate);


AllData:

LOAD Distinct Name Resident Data;

Outer Join (AllData) LOAD RequiredDate Resident AllDates;

DROP Table AllDates;

Left Join (AllData) LOAD Date1 as RequiredDate, Name, Count Resident Data;

DROP Table Data;


Result:

NoConcatenate

LOAD RequiredDate, Name, If(IsNull(Count),0,Count) as Count;

LOAD * Resident AllData;

DROP Table AllData;

stevelord
Valued Contributor

Re: Line Graph, put 0s where no data present for activity date?

Working to implement this now and will let you know how it goes.  Thanks!  (I'll switch from helpful to correct answer for you after I can confirm and let the community know if I come across some loose-end detail.  I'll give you correct and me helpful if I just find some little wrinkle to fix. )

stevelord
Valued Contributor

Re: Line Graph, put 0s where no data present for activity date?

Hi, I almost have it through.  It gets an error message at this point:

------------

Field not found - <<=>

AllDates: 

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

AutoGenerate 1 

While +IterNo()-1 <=

-----------------------------

It looks like a simple syntax thing, and I think I have the rest laid out correctly.

Anyone have an idea how to fix the syntax wrinkl mentioned in the above error message?

nagaiank
Valued Contributor III

Re: Re: Line Graph, put 0s where no data present for activity date?

I have attached the sample application using my code.

Make sure vMinDate and vMaxDate has date as numbers.

If you are using formatted date string such as 05/01/2014, convert the dates to mumbers. For example

vMinDate = Num(Date#('05/01/2014','MM/DD/YYYY'));

vMaxDate = Num(Date#('05/03/2014','MM/DD/YYYY'));

Hope this helps.

stevelord
Valued Contributor

Re: Re: Line Graph, put 0s where no data present for activity date?

Thanks!  The first half of your script looks like it was all that was needed, and maybe not the join and reload stuff.  I found this video which did what you did up to a point, but the narrator just let the source data join itself to the Calendar table he created on the date field in common.

Creating A Master Calendar

Now I will say, I banged my head on this for hours, and ran around in circles, and ultimately made a CalendarHack.xlsx worksheet with a FileDate field with 20 years of dates in it, and a CHValue field with 0 values, then made an if statement to sum the CHValues if no value on the real table.  When THAT didn't work, I knew it wasn't the fault of the script or the expressions and wandered around in my line graph properties.  I fell on 'suppress 0 values' and 'omit missing values' check boxes.  I UNCHECKED THOSE and BOOM all the days with 0s showed up!  I commented out my calendar hack file, then uncommented the varmindate varmaxdate temp master calendar stuff, and the 0s were still present.

So, I can confirm the calendar stuff you presented works but that maybe you're okay letting it join itself on the common key field rather than the extra rounds of joins.  Additionally, users will need to uncheck the suppress 0 and uncheck the omit missing data checkboxes to let the 0s shine through on the line graph.

Actually, I'll give you the correct answer credit and meself a helpful for finding this extra video and figuring out the check boxes needed to let the 0s on the chart after the script created the missing days.

Community Browser