topic Re: Line Graph Help in QlikView

Line Graph Help

joshrussin — Wed, 25 Nov 2020 16:16:04 GMT

Hey guys,

I have a line graph that I am trying to show items produced per month. Currently it only shows Jan through September because we haven't collected any data for the rest of the year (Oct, Nov, Dec). I was curious to see how I can get those months to show up anyways with no data collected so far. Here is the current formula I am using to show the total per month.

Sum({$<Program_Dash={'Mechanical Inspection'}, Status_Dash={'Completed'}, Year = {"$(=Year(FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays))))"}>} Processed_Dash)

Re: Line Graph Help

Anil_Babu_Samineni — Wed, 20 Sep 2017 02:53:44 GMT

I believe you have to generate Master Script to get missing Dates thru Months until whole year of months then in front end you have to tick the Suppress from Presentation tab to show the null values in Graph..

Re: Line Graph Help

joshrussin — Wed, 20 Sep 2017 13:49:01 GMT

I've never used this before. Any tips?

Re: Line Graph Help

mostwanted123 — Wed, 20 Sep 2017 13:59:51 GMT

Hope this helps,

How to use - Master-Calendar and Date-Values

Creating A Master Calendar

Regards,

Pratik

Re: Line Graph Help

pradosh_thakur — Wed, 20 Sep 2017 14:06:09 GMT

what is the dimension you are using?

month ?

if so

can you please go to the presentation tab and un-check suppress zero values.

regards

Pradosh

Re: Line Graph Help

Anil_Babu_Samineni — Wed, 20 Sep 2017 14:10:04 GMT

Try this Master Calendar Generation Script

Re: Line Graph Help

mostwanted123 — Wed, 20 Sep 2017 14:17:50 GMT

Also,

After you create the master calendar script,

use the 'month' field as dimension and and un-check suppress zero values in the Presentation tab.

In the expression,

Sum({$<Program_Dash={'Mechanical Inspection'}, Status_Dash={'Completed'}>} Processed_Dash)

Regards,

Pratik

Re: Line Graph Help

joshrussin — Wed, 20 Sep 2017 14:54:22 GMT

This is where I am at on this. I am getting an error on the From part.

LOAD

Date_Dash,

Date(Date_Dash) as Date_MS,

Month(Date_Dash) as Month,

Num(Month(Date_Dash)) as MonthNum,

Year(Date_Dash) as Year,

MakeDate(year(Date_Dash)) as vCY,

MakeDate(month(Date_Dash)) as vCM,

Min(Date_Dash) AS MinDate,

Max(Date_Dash) as MaxDate,

If(Processed_Dash < Daily_Target_Dash, 1,0) as Daily_Target_Flag,

Program_Dash,

UOM_Dash,

Status_Dash,

Ave_Cost_Dash,

Processed_Dash,

Daily_Target_Dash,

Emp_Act_Dash,

Rev_Diff_Dash;

Load Date(MinDate +IterNo()-1 ) AS MasterCal While (MinDate + IterNo() -1) <= Num(MaxDate);

FROM [lib://Data files/OB_Summary_Data.xlsx]

(ooxml, embedded labels, table is OB_Dash_Data);

Re: Line Graph Help

Anil_Babu_Samineni — Wed, 20 Sep 2017 14:57:52 GMT

You missed this part?

Re: Line Graph Help

joshrussin — Wed, 20 Sep 2017 15:06:50 GMT

In the first attached, I added that to my original LOAD instead because I get this error when I try like that.

Re: Line Graph Help

mostwanted123 — Wed, 20 Sep 2017 15:21:33 GMT

Try this,

Temp_Calendar_Range:

LOAD

Num(Min(Date_Hash)) as MinDate,

Num(Max(Date_Hash)) as MaxDate

FROM [lib://Data files/OB_Summary_Data.xlsx];

LET vStartDate=Peek('MinDate', 0, 'Temp_Calendar_Range');

LET vEndDate=Peek('MaxDate', 0, 'Temp_Calendar_Range');

Calendar:

LOAD * ,

Year(CalDate1) AS CalYear, // Standard Calendar Year

Month(CalDate1) AS CalMonth,// Standard Calendar Month

Date(CalDate1,'MMM') as CalMonthName, // Standard Calendar Month Name

'Q' &'-'& Ceil(Month(CalDate1)/3) as CalQuarter; // Standard Calendar Quarter

LOAD

Date($(vStartDate) + IterNo() - 1) as CalDate1

AUTOGENERATE (1)

WHILE $(vStartDate) + IterNo() - 1 <= $(vEndDate);

Regards,

Pratik

Re: Line Graph Help

joshrussin — Wed, 20 Sep 2017 15:31:57 GMT

Alright, that script loaded, what do I use as my expression for the graph? I tried CalMonth and it just returned the sum of all data for Jan - Sept.

Re: Line Graph Help

mostwanted123 — Wed, 20 Sep 2017 15:43:29 GMT

In the expression,

Sum({$<Program_Dash={'Mechanical Inspection'}, Status_Dash={'Completed'}>} Processed_Dash)

Uncheck the 'Suppress Zero Values' in the Presentation tab.

Regards,

Pratik

Re: Line Graph Help

joshrussin — Wed, 20 Sep 2017 15:55:20 GMT

This is all I get under Presentation.

Re: Line Graph Help

mostwanted123 — Wed, 20 Sep 2017 16:01:20 GMT

Sorry for the misunderstanding Josh.

I was suggesting this for QlikView.

For QlikSense, Go to Add-ons ---> Data Handling ---> Check Include Zero Values

Regards,

Pratik

Re: Line Graph Help

joshrussin — Wed, 20 Sep 2017 16:08:56 GMT

For the Dimension am I still supposed to use Month?

Re: Line Graph Help

mostwanted123 — Wed, 20 Sep 2017 16:09:38 GMT

Yes

Re: Line Graph Help

joshrussin — Wed, 20 Sep 2017 16:10:32 GMT

Here is my app if you'd like to take a look.

Re: Line Graph Help

mostwanted123 — Wed, 20 Sep 2017 16:23:14 GMT

As your data is only till Sept, it is generating months only till Sept and not showing Oct, Nov, Dec.

For this try,

vEndDate = Num(Date(Yearend(today()))); //instead of Peek('MaxDate', 0, 'Temp_Calendar_Range');

Regards,

Pratik

Re: Line Graph Help

joshrussin — Wed, 20 Sep 2017 17:05:46 GMT

I tried this and still only shows through sept.