Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

joshrussin
Contributor II

Graph Help with Months

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! ! !

1 Solution

Accepted Solutions
joshrussin
Contributor II

Re: Graph Help with Months

Processed Line:

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

Goal Line:

=if(WildMatch(CalMonth,'Jan'),840,

if(WildMatch(CalMonth,'Feb'),800,

    if(WildMatch(CalMonth,'Mar'),920,

    if(WildMatch(CalMonth,'Apr'),800,

    if(WildMatch(CalMonth,'May'),880,

    if(WildMatch(CalMonth,'Jun'),840,

    if(WildMatch(CalMonth,'Jul'),800,

    if(WildMatch(CalMonth,'Aug'),920,

    if(WildMatch(CalMonth,'Sep'),800,

    if(WildMatch(CalMonth,'Oct'),880,

    if(WildMatch(CalMonth,'Nov'),800,

    if(WildMatch(CalMonth,'Dec'),760

    ))))))))))))

Dimension:

CalMonth

KPI_0244.jpg

30 Replies
MVP
MVP

Re: Graph Help with Months

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?

joshrussin
Contributor II

Re: Graph Help with Months

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

vvira1316
Valued Contributor II

Re: Graph Help with Months

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;

joshrussin
Contributor II

Re: Graph Help with Months

KPI_0234.jpg

vvira1316
Valued Contributor II

Re: Graph Help with Months

Hi,

Can you please provide sample files.

OB_Summary_Data.xlsx

Thanks,

Vijay

joshrussin
Contributor II

Re: Graph Help with Months

Here ya go

vishsaggi
Esteemed Contributor III

Re: Graph Help with Months

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

vvira1316
Valued Contributor II

Re: Graph Help with Months

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;

joshrussin
Contributor II

Re: Graph Help with Months

Still only shows until september