topic calendar question in QlikView

calendar question

josephinetedesc — Thu, 14 Sep 2017 05:27:24 GMT

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?

Re: calendar question

Anil_Babu_Samineni — Thu, 14 Sep 2017 05:31:14 GMT

May be this? Check Date format

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

Re: calendar question

josephinetedesc — Thu, 14 Sep 2017 05:34:02 GMT

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

Re: calendar question

Anil_Babu_Samineni — Thu, 14 Sep 2017 05:44:12 GMT

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

Re: calendar question

josephinetedesc — Thu, 14 Sep 2017 05:49:04 GMT

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.

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

Re: calendar question

Anil_Babu_Samineni — Thu, 14 Sep 2017 05:54:05 GMT

You marked as Assumed Answered that means you got solution,

Re: calendar question

josephinetedesc — Thu, 14 Sep 2017 05:55:10 GMT

sorry I the mouse ran away from me!

Re: calendar question

Anil_Babu_Samineni — Thu, 14 Sep 2017 05:59:32 GMT

NP, Here is that date is not correct?

Re: calendar question

josephinetedesc — Thu, 14 Sep 2017 06:02:58 GMT

20 August 2016 is the date (today - 390)

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

Re: calendar question

Anil_Babu_Samineni — Thu, 14 Sep 2017 06:07:14 GMT

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

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

Re: calendar question

its_anandrjs — Thu, 14 Sep 2017 06:08:41 GMT

Then try some of this

This gives Today month minus 13 months and output will be 13 month back Day

LET vMinDate = Day(AddMonths(Today(), -13));

This gives Date after 13 months back

LET vMinDate = Date(AddMonths(Today(), -13));

This gives Year after 13 months back

LET vMinDate = Year(AddMonths(Today(), -13));

Re: calendar question

josephinetedesc — Thu, 14 Sep 2017 06:31:07 GMT

something odd happens and I will need to explore:

when I use this expresssion

LET vMinDate = Day(AddMonths(Today(), -13));

I then get months appearing all the way back to 2011 - but this could be some other 'glitch'.

It works when hard code date (today - 440) - but obviously not a good idea!

I will work on it some more ...

but

when I add this to a text box = Day(AddMonths(Today(), -13)) I get 14???

and this added to a text box =Date(AddMonths(Today(), -13))) does not work ...