topic Re: Graph Help with Months in QlikView

Graph Help with Months

joshrussin — Wed, 20 Sep 2017 18:10:11 GMT

I am trying to achieve a graph that shows all the months, regardless of the data. I am showing the sum of processed per month in the graph. Right now it only shows up until September (Current month), but I also want to have Oct, Nov, Dec in there as well although they will just be zeros. This is the app I currently am working with. If there are any alternate solutions, I am open. Thanks! ! !

Re: Graph Help with Months

sunny_talwar — Wed, 20 Sep 2017 18:59:47 GMT

You have a got a unique data model where the master calendar is not connected to the fact table... is this on purpose?

Also, in your sample you just don't have the last three months.... may be use master calendar to the end of the year?

Re: Graph Help with Months

joshrussin — Wed, 20 Sep 2017 19:11:40 GMT

I am new to Master Calendar. How would I connect it? How could I go about trying this?

Re: Graph Help with Months

vvira1316 — Wed, 20 Sep 2017 20:31:01 GMT

Hi,

I'm unable to check following without sample data file of yours.

Following has Calendar. if use MonthName(CalMonth) then most likely you will have all months. you may have to uncheck suppress null and/or zero flags

Copy Paste following in your load editor

//This is where to add new holiday hours
SET vHolidays = '1/2/2017', '5/29/2017', '7/4/2017', '11/23/2017', '11/24/2017', '12/25/2017', '12/26/2017';

//Loads data from OB_Summary_Data file
//Sheet OB_Dash_Data
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);
//Load
// Min(Date_Dash) as MinDate,
// Max(Date_Dash) as MaxDate
FROM [lib://Data files/OB_Summary_Data.xlsx]
(ooxml, embedded labels, table is OB_Dash_Data);

//////////////////////////////
//Testing Master Calendar

Temp_Calendar_Range:
LOAD
Num(Min(Date_Dash)) as MinDate,
Num(Max(Date_Dash)) as MaxDate
FROM [lib://Data files/OB_Summary_Data.xlsx]
(ooxml, embedded labels, table is OB_Dash_Data);

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

LET Start = floor(YearStart(peek('MinDate')));
LET End = floor(YearEnd(peek('MaxDate')));
Drop Table tmp;

LET NumOfDays = End - Start + 1;

Date_src:
LOAD
$(Start) + Rowno() - 1 as DateID
AUTOGENERATE $(NumOfDays);

[MasterCalendar]:
LOAD
DateID as Date_Dash, // just in case
date(DateID) as CalDate, // it will be in format defined in your SET DateFormat=, or in your system format
day(DateID) as CalDay,
week(DateID) as CalWeek,
month(DateID) as CalMonth, // simple month name; it is dual - numeric and text
dual(month(DateID) & '-' & year(DateID),
year(DateID) & num(month(DateID), '00')) as CalMonthYear, // Month-Year format, dual
year(DateID) as CalYear,
weekday(DateID) as CalWeekday,
'Q' & ceil(month(DateID)/3) as CalQuarter, // in format Q1, Q2, Q3, Q4
// dual('Q' & ceil(month(DateID)/3) & '-' & year(DateID)) as MonthQuarter,
year(DateID) & ceil(month(DateID)/3) as CalQtrYear // Qn-Year, dual
// and whatever else you may want here...
RESIDENT Date_src;

Drop Table Date_src;

Drop Table Temp_Calendar_Range;

Re: Graph Help with Months

joshrussin — Wed, 20 Sep 2017 20:39:11 GMT

Re: Graph Help with Months

vvira1316 — Wed, 20 Sep 2017 20:40:52 GMT

Hi,

Can you please provide sample files.

OB_Summary_Data.xlsx

Thanks,

Vijay

Re: Graph Help with Months

joshrussin — Wed, 20 Sep 2017 20:49:11 GMT

Here ya go

Re: Graph Help with Months

vishsaggi — Wed, 20 Sep 2017 20:49:40 GMT

Try this calendar script.

Create a field in your facts table for the date you want to use. Like

LOAD col1, col2,

Num(Floor(YourdateFieldFact)) AS %KeyField

FROM yoursource;

Calendar:

Load

Num(Floor(TempDate)) AS %KeyField,

'W' & week(TempDate) AS _Week,

Year(TempDate) AS _Year,

Month(TempDate) AS _Month,

Day(TempDate) AS _Day,

YeartoDate(TempDate)*-1 AS _CurYTDFlag,

YeartoDate(TempDate,-1)*-1 AS _LastYTDFlag,

date(monthstart(TempDate), 'MMM-YYYY') AS _MonthYear,

'Q' & ceil(month(TempDate) / 3) AS _Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) AS _WeekYear,

Date(TempDate, 'MMM-DD') AS _MonthDay,

WeekDay(TempDate) AS _WeekDay

;

//=== Generate a temp table of dates ===

LOAD

date(mindate + IterNo()) AS TempDate,

maxdate // Used in InYearToDate() above, but not kept

WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/

LOAD

min(FieldValue('YourDateFieldFact', recno()))-1 AS mindate,

max(FieldValue('YourDateFieldFact', recno())) AS maxdate

AUTOGENERATE FieldValueCount('YourDateFieldFact');

Re: Graph Help with Months

vvira1316 — Wed, 20 Sep 2017 20:50:15 GMT

make following changes

LET Start = floor(YearStart(peek('MinDate', 0, 'Temp_Calendar_Range')));

LET End = floor(YearEnd(peek('MaxDate', 0, 'Temp_Calendar_Range')));

//Drop Table tmp;

Re: Graph Help with Months

joshrussin — Wed, 20 Sep 2017 20:52:26 GMT

Still only shows until september

Re: Graph Help with Months

vvira1316 — Wed, 20 Sep 2017 21:31:49 GMT

Hi Joshua,

I think it is QlikSense feature that it will not show those months. If we do the same chart in QlikView then you will see remaining months.

As can be seen in screen shot below.

Re: Graph Help with Months

joshrussin — Thu, 21 Sep 2017 13:07:37 GMT

Can you send me the qlikview app?

Re: Graph Help with Months

joshrussin — Thu, 21 Sep 2017 13:12:41 GMT

Ok, So I changed the dimension to CalMonth and shows all months.

This is my expression, and it only shows the total for everything each month rather than the total for each month.

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

Re: Graph Help with Months

vvira1316 — Thu, 21 Sep 2017 14:20:59 GMT

here it is

Re: Graph Help with Months

joshrussin — Thu, 21 Sep 2017 14:30:40 GMT

Yeah, I put your script in and just shows till Sept. Can you take a look at the one i sent?

Re: Graph Help with Months

joshrussin — Thu, 21 Sep 2017 14:31:34 GMT

Hey Vishwarath,

Can you take a look at my app?

Re: Graph Help with Months

vvira1316 — Thu, 21 Sep 2017 14:43:06 GMT

Hi Joshua,

I'm looking at your file "graph help (1).qvf". Will update.

BR,

Vijay

Re: Graph Help with Months

vishsaggi — Thu, 21 Sep 2017 14:59:34 GMT

Sure just give me few minutes.

Re: Graph Help with Months

vvira1316 — Thu, 21 Sep 2017 15:07:30 GMT

Hi Joshua,

is there any reason you are not linking your calendar to fact table?

Re: Graph Help with Months

joshrussin — Thu, 21 Sep 2017 15:25:14 GMT

What do you mean?