Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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! ! !
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
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?
I am new to Master Calendar. How would I connect it? How could I go about trying this?
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;
Hi,
Can you please provide sample files.
OB_Summary_Data.xlsx
Thanks,
Vijay
Here ya go
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');
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;
Still only shows until september