Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all
this is working beautifully BUT
in my Calendar tab I hard code these 2 lines - I would prefer not to!
LET vDateMin = Num(MakeDate(2012,07,01));
LET vDateMax = Num(MakeDate(2016,09,01))
TempCalendar:
LOAD
Date($(vDateMin) + RowNo() - 1) as DateFull,
Year(Date($(vDateMin) + RowNo() - 1)) as Date_YearNum,
Monthname(Date($(vDateMin) + RowNo() - 1)) as Date_MonthNameTemp,
Num(Month(Date($(vDateMin) + RowNo() - 1))) as Date_MonthNum,
Num(Day(Date($(vDateMin) + RowNo() - 1))) as Date_DayNum,
WeekDay(Date($(vDateMin) + RowNo() - 1)) as Date_DayName,
WeekDay(Date($(vDateMin) + RowNo() - 1)) as ISOWeekDay,
WeekEnd(Date($(vDateMin) + RowNo() - 1)) as Date_WeekEnding
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1 <= $(vDateMax);
I have tried changing
LET vDateMax = Num(MakeDate(2016,09,01)) and it fails (see error Message)
Error Message
Field not found - <<=>
TempCalendar:
LOAD
Date(41091 + RowNo() - 1) as DateFull,
Year(Date(41091 + RowNo() - 1)) as Date_YearNum,
Monthname(Date(41091 + RowNo() - 1)) as Date_MonthNameTemp,
Num(Month(Date(41091 + RowNo() - 1))) as Date_MonthNum,
Num(Day(Date(41091 + RowNo() - 1))) as Date_DayNum,
WeekDay(Date(41091 + RowNo() - 1)) as Date_DayName,
WeekDay(Date(41091 + RowNo() - 1)) as ISOWeekDay,
WeekEnd(Date(41091 + RowNo() - 1)) as Date_WeekEnding
AUTOGENERATE 1
WHILE 41091+IterNo()-1 <=
I know that the Max(DateFull) and Min(DateFull) and MaxString(DateFull) and Minstring(DateFull) work when the load has finished ... even following the error message - as shown by the text below
There was a problem with the dates: below is how the date EDT comes into the script 2015-08-26 13:58:00
I have made this: Date(Floor(EDT))) as DateFull
full script for Calendar:
//LET vDateMin = Num(MakeDate(2012,07,01));
//LET vDateMax = Num(MakeDate(2016,08,01));
LET vDateMin = Num(MakeDate(2012,07,01));
LET vDateMax = Num(MakeDate(2016,09,01));
//LET vDateMax = Num(Max(MakeDate(DateFull)));
TempCalendar:
LOAD
Date($(vDateMin) + RowNo() - 1) as DateFull,
Year(Date($(vDateMin) + RowNo() - 1)) as Date_YearNum,
Monthname(Date($(vDateMin) + RowNo() - 1)) as Date_MonthNameTemp,
Num(Month(Date($(vDateMin) + RowNo() - 1))) as Date_MonthNum,
Num(Day(Date($(vDateMin) + RowNo() - 1))) as Date_DayNum,
WeekDay(Date($(vDateMin) + RowNo() - 1)) as Date_DayName,
WeekDay(Date($(vDateMin) + RowNo() - 1)) as ISOWeekDay,
WeekEnd(Date($(vDateMin) + RowNo() - 1)) as Date_WeekEnding
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1 <= $(vDateMax);
join
Fin_Dates_Temp:
LOAD * INLINE
[
Date_MonthNum, FinMonthNum, Quarter
7, 1, 1
8, 2, 1
9, 3, 1
10, 4, 2
11, 5, 2
12, 6, 2
1, 7, 3
2, 8, 3
3, 9, 3
4, 10, 4
5, 11, 4
6, 12, 4
];
Calendar:
LOAD *,
if(FinMonthNum >0 and FinMonthNum<=6,
Date_YearNum & '/' & Mid((Date_YearNum+1),3,2),
(Date_YearNum-1) & '/' & Mid(Date_YearNum,3,2)) as Date_Fin,
if(FinMonthNum >0 and FinMonthNum <=6,
Mid(Date_YearNum,3,2) & '/' & Mid((Date_YearNum+1),3,2),
Mid((Date_YearNum-1),3,2) & '/' & Mid(Date_YearNum,3,2)) as Date_FinancialYearShrt2,
'Q' & Quarter & '_' & if(FinMonthNum >0 and FinMonthNum<=6,
Date_YearNum & '/' & Mid((Date_YearNum+1),3,2),
(Date_YearNum-1) & '/' & Mid(Date_YearNum,3,2)) as Date_QuartFinYear,
if(ISOWeekDay>=5,'View Saturdays only','Remove Saturdays') as IsWeekEnd
Resident TempCalendar;
DROP Tables TempCalendar
;
Change it to
LET vDateMin = Min(Date(EDT));
LET vDateMax = Max(Date(EDT));
And then Check in text object how this coming and can you confirm me one thing whether what is the start date and what is the end date?
Hi Anil
it still falls over at the same point.
=maxstring(DateFull) & ' Max DateFull-\n ' &
minstring(DateFull)& ' Min DateFull -\n ' &
maxstring(date(EDT))& 'Max date(EDT)' &
minstring(date(EDT))& ' Min Date(EDT) -\n ' &
Jo
Hi Tamil
I have done a cutdown version and posted it.
Jo
Hi Jo,
Have a look at the attached file.
Here's example script to build a calendar extremely quickly from the min and max values of a date field. It doesn't require a resident load, a temporary table, or variables.
Calendar:
LOAD *
,date(monthstart(Date),'MMM YYYY') as Month
,date(yearstart(Date),'YYYY') as Year
;
LOAD Min+iterno()-1 as Date
WHILE Min+iterno()-1 <= Max
;
LOAD min(fieldvalue('Date',recno())) as Min
, max(fieldvalue('Date',recno())) as Max
AUTOGENERATE fieldvaluecount('Date')
;
Hi Jo,
I have attached a solution in the below post yesterday. Kindly check and let us know if any issues. Happy weekend.
Hi Tamil
Ok I see what you have done: I will check I am not sure that the financial year is working ... (after all I haven't looked at it since last year
Jo
//=======================================================================
MinMaxDate:
Load Min(DateFull) as MinDate,
Max(DateFull) as MaxDate
Resident Part1 Order by DateFull;
Let vDateMin = Num(Monthstart(AddMonths(Peek('MinDate',0,'MinMaxDate'),1)));
Let vDateMax = Num(Monthstart(Peek('MaxDate',0,'MinMaxDate')));
DROP Table MinMaxDate;
//=======================================================================