30 Replies Latest reply: Sep 21, 2017 4:20 PM by Joshua Russin RSS

    Graph Help with Months

    Joshua Russin

      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

          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
              Joshua Russin

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

                • Re: Graph Help with Months
                  Vijay Vira

                  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
                Vishwarath Nagaraju

                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
                  Vijay Vira

                  Not sure if you placed your new question MasterCalendar help in following

                   

                  New to Qlik Sense