topic Re: Creating A Date Field in QlikView

Creating A Date Field

Anonymous — Fri, 01 Dec 2017 08:48:34 GMT

Hello Guys,

I want to create a field in my calendar that will hold the following:

For example

29/11/2017

29/11/2016

30/11/2017

30/11/2016

01/12/2017

01/12/2016

02/12/2017

02/12/2016

03/12/2017

03/12/2016

N.B. this field should be dynamic means getting today's date and back to 2 days and forward to 2 days

Regards,

Kevork

Re: Creating A Date Field

shraddha_g — Fri, 01 Dec 2017 11:34:53 GMT

Try

Let varMinDate = Num(today()-2);

Let varMaxDate = Num(today()-2);

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

TempDate AS OrderDate,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Re: Creating A Date Field

Anil_Babu_Samineni — Fri, 01 Dec 2017 11:35:26 GMT

This will deserve it?

LOAD Date((DateField - 2) + IterNo() -1) as Date

While Date((DateField - 2) + IterNo() -1) <= Date(Today()+2);

LOAD Today() as DateField

AutoGenerate 1;

Re: Creating A Date Field

Anonymous — Fri, 01 Dec 2017 11:48:29 GMT

Guys I already have a master calendar( but I just want to add a field holding just 5 dynamic days data embedded to the same master calendar.

Re: Creating A Date Field

sunny_talwar — Fri, 01 Dec 2017 16:53:20 GMT

May be create a flag within your master calendar for these dates

MasterCalendar:

LOAD ...,

If(TempDate >= Today() - 2 and TempDate <= Today() + 2, 1, 0) as [PlusMinus2DaysFlag]

Resident ....;

Re: Creating A Date Field

Anonymous — Fri, 01 Dec 2017 18:02:51 GMT

‌guys sorry but i wasn’t clear with my issue. The problem is that i want to use the field as a dimension later in a bar chart where i want to show the result as -2 days 01/12/2017-01/12/2016 + 2 days in the same sequence. The idea is to forecast the upcoming 2 days sales compared to previous year’s same dates. So, the field must contain dynamic 10 rows 5 for the current date and 5 same dates of previous year.