Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a date value that I pulled the MaxRevMonth & MinRevMonth values from a resident table and want to use them in my Master Calendar.
For some reason it errors out saying "Field not found". Seems like a simple thing, what I'm I missing ?
ForecastRev:
LOAD date(floor("Report EOM")) as [Report EOM Rev] ,
date(floor(ReportRundate)) as [ReportRundate_Rev],
Project,
date(floor(RevMonth)) as RevMonth,
//date(floor(Max(RevMonth))) as MaxRevMonth,
//date(floor(Min(RevMonth))) as MinRevMonth,
RevAmt,
RevType,
WorkLocation,
BacklogType as [BacklogType_Rev];
//group by Project;
SQL SELECT "Report EOM",
ReportRundate,
Project,
RevMonth,
RevAmt,
RevType,
WorkLocation,
BacklogType
FROM CorpReporting.MonthlyBacklogForecastRev;
//Concatenate (ForecastRev)
MaxMinRevMonth:
Load
date(floor(Max(RevMonth))) as MaxRevMonth,
date(floor(Min(RevMonth))) as MinRevMonth
Resident ForecastRev;
//****************************************************** New Tab
LET vDateMin = MinRevMonth;
LET vDateMax = MaxRevMonth;
TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
Calendar:
LOAD
Date(TempDate) AS CalendarDate,
// Standard Date Objects
Day(TempDate) AS CalendarDayOfMonth,
WeekDay(TempDate) AS CalendarDayName,
Week(TempDate) AS CalendarWeekOfYear,
Month(TempDate) AS CalendarMonthName,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
Year(TempDate) AS CalendarYear,
The peek function is necessary to be able to ..well, peek into a table.
Change the lines where you create the variables to:
LET vDateMin = peek('MinRevMonth');
LET vDateMax = peek('MaxRevMonth');
For some reason it only output one row for calendar withese dates even though its uppose to 2021. That date is malformedI guess.
I typically shown them in MM/DD/YYY any ideas. Yet when I drop it into a text box it shows another date range?
Max / Min from variables
If RevMonth doesn't contain a date but a month then you'll start at the date with the numeric value of the month. That's probably 1, which is date 31-12-1899 and your calendar will end at most 11 days later.
hiiii
write this
Load
date(floor(Max(Date(RevMonth),MM/DD/YYY))) as MaxRevMonth,
date(floor(Min(Date(RevMonth),MM/DD/YYY))) as MinRevMonth
Didn't seem to help. But I understand the suggestion. I looked at other Calendars and the code seems to be correct.
The peak based variables are outputting something totaly different than the source. Even after the date formatting.
Makes sense. I need to rename the field thrn. Not sure why its called RevMonth when it has a full date. Infact i remvoed the time stamp using Floor().