Date Problem - I can only make it work if hard coded

[josephinetedesc]

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

;

[Anil_Babu_Samineni]

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?

[josephinetedesc]

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

[josephinetedesc]

Hi Tamil

I have done a  cutdown version and posted it.

Jo

[tamilarasu]

Hi Jo,

Have a look at the attached file.

[johnw]

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')
;

[tamilarasu]

Hi Jo,

   I have attached a solution in the below post yesterday. Kindly check and let us know if any issues. Happy weekend.

[josephinetedesc]

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;

//=======================================================================