calendar question

[josephinetedesc]

Hi All

LET vDateMin = Num(Date(Floor(Today()- 390)));

This works but hard codes the fact that I want (-13 months).

390 is close enough but not accurate!  Is there a way I can ju8st take away 13 months?

Jo

[Anil_Babu_Samineni]

May be this? Check Date format

LET vDateMin = AddMonths(Today(), -13);

[josephinetedesc]

but ... I want the number to be in days ... for the calendar?

[Anil_Babu_Samineni]

Remove floor and check whether you are getting accuracy date or not?

[josephinetedesc]

this is my calendar ...  basically i only want 13 months of data - so that January last year can always be compared to January this year.  Off course I then want Financial years which in Australia start July 1 to Jun 30 of the following year.

Jo

//Let vDate =Date(Floor(Today()),'YYYY-MM-DD');

Let vDate =Date(Floor(Today()));

//LET vDateMax = Num(MakeDate($(vDate)));

LET vDateMax = Date(Floor(Today()));

//LET vDateMin = Date(Floor(Today()- 390),'YYYY-MM-DD');

LET vDateMin = Num(Date(Floor(Today()- 390)));

//Load All Dates Between Years - Update For Dataset  LET vDateMin = Num(MakeDate(2013,07,01));

//// LET vDateMax = Floor(today());  //LET vDateMax = Num(MakeDate(2017,09,01));

//Update for Current Financial Year

//SET vfinYear = '1519';

//Let vLastfinYear = (Num(left($(vfinYear), 2))-1) & (Num(right($(vfinYear), 2))-1);

Fin_Dates_Temp:

LOAD * INLINE

[

    MonthNm, FinMonthNm, Quarter,JoMonthNm

    Jul, 01, 1,07

    Aug, 02, 1,08

    Sep, 03, 1,09

    Oct, 04, 2,10

    Nov, 05, 2,11

    Dec, 06, 2,12

    Jan, 07, 3,01

    Feb, 08, 3,02

    Mar, 09, 3,03

    Apr, 10, 4,04

    May, 11, 4,05

    Jun, 12, 4,06

];

Holidays:

Load Holiday as DateFull, Holiday inline

[Holiday

8/06/2015

2/10/2015

3/11/2015

25/12/2015

28/12/2015

1/01/2016

26/01/2016

14/03/2016

25/03/2016

26/03/2016

27/03/2016

28/03/2016

25/04/2016

13/6/2016

30/9/2016

01/11/2016

25/12/2016

26/12/2016

27/12/2016

26/1/2017

13/3/2017

14/4/2017

15/4/2017

16/4/2017

17/4/2017

25/4/2017

];

LET vDateToday = Num(Today());

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,

    WeekName(Date($(vDateMin) + RowNo() - 1)) as DateWeekName,

    WeekStart(Date($(vDateMin) + RowNo() - 1)) as DateWeekStart

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1 <= num(today());

Left Join(TempCalendar)

LOAD

    num(month(date#(MonthNm,'MMM'))) as Date_MonthNameTemp,

    FinMonthNm as Date_FinancialMonthNum,

    Quarter as Date_Quarter,

    JoMonthNm as jojoMonthNm

Resident Fin_Dates_Temp;

Calendar:

LOAD *,

     if(Date_FinancialMonthNum >0 and Date_FinancialMonthNum<=6,

         Date_YearNum  & '-' & (Date_YearNum+1),

         (Date_YearNum-1) & '-' & Date_YearNum) as Date_FinancialYearName,

     if(Date_FinancialMonthNum >0 and Date_FinancialMonthNum<=6,

         Date_YearNum  & '/' & Mid((Date_YearNum+1),3,2),

         (Date_YearNum-1) & '/' & Mid(Date_YearNum,3,2)) as Date_Fin,

     if(Date_FinancialMonthNum >0 and Date_FinancialMonthNum<=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_FinShrt2,

     'Q' & Date_Quarter & '_' & if(Date_FinancialMonthNum >0 and Date_FinancialMonthNum<=6,

         Date_YearNum  & '/' & Mid((Date_YearNum+1),3,2),

         (Date_YearNum-1) & '/' & Mid(Date_YearNum,3,2)) as Date_QuartFinYear,    

         'Q' & Date_Quarter as DateQuarter,        

     Dual(Date_MonthNameTemp, Date_FinancialMonthNum) as Date_Fin_Month,

                            Exists(Holiday,DateFull) as IsHoliday,

        if(ISOWeekDay>=5,'View Saturdays only','Remove Saturdays') as IsWeekEnd

Resident TempCalendar;

DROP Tables TempCalendar, Fin_Dates_Temp;

TAG Field Date_FinancialYearShrt2 With '$hidden';

[Anil_Babu_Samineni]

You marked as Assumed Answered that means you got solution,

[josephinetedesc]

sorry I the mouse ran away from me!

[Anil_Babu_Samineni]

NP, Here is that date is not correct?

[josephinetedesc]

20 August 2016 is the date (today - 390)

14/09/2017	42992
20/08/2016	42602

[Anil_Babu_Samineni]

That is because, You are using Num() over variable? You can remove that and then try

LET vDateMin = Date(Today()- 390);