Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
joshrussin
Creator III
Creator III

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
Creator III
Creator III
Author

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

View solution in original post

30 Replies
sunny_talwar

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
Creator III
Creator III
Author

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

vvira1316
Specialist II
Specialist II

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
Creator III
Creator III
Author

KPI_0234.jpg

vvira1316
Specialist II
Specialist II

Hi,

Can you please provide sample files.

OB_Summary_Data.xlsx

Thanks,

Vijay

joshrussin
Creator III
Creator III
Author

Here ya go

vishsaggi
Champion III
Champion III

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
Specialist II
Specialist II

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
Creator III
Creator III
Author

Still only shows until september