Filter

[#1]

Hallo Zusammen,

in my sheet is a column with the information like this:

Date

30.06.2008

01.07.2008

02.07.2008

03.07.2008

but the sheet is connected with the SQL server, so i can't change the type.

How it's possible that i can create a filter for

Year

Month

Day

[Gysbert_Wassenaar · Accepted solution]

Yes, create a derived calendar in the script to create Year, Month and Day fields: Derived fields ‒ Qlik Sense

[shraddha_g]

Year - > year(date(Date#(Datefield,'DD.MM.YYYY')))

Month - >Month(date(Date#(Datefield,'DD.MM.YYYY')))

Day - Day(date(Date#(Datefield,'DD.MM.YYYY')))

[PrashantSangle]

Hi,

yes use qlikview function Year(),Monthname() and Day()

Regards,

[#5]

I tried with this code but it is not working. What is wrong with the code or what can i change?

Calendar:

  Declare Field Definition Tagged '$date'

    Parameters

    first_month_of_year = 1

    Fields

    Year($1) as Year Tagged ('$numeric'),

    Month($1) as Month Tagged ('$numeric'),

    DayNumberOfYear($1, first_month_of_year) as DayNumberOfYear Tagged ('$Numeric');

[Gysbert_Wassenaar]

Did you add a line too to create the calendar based on your date field?

DERIVE FIELDS FROM FIELDS YourDateFieldHere USING Calendar;

[#7]

where i can add the line exactly?

[Gysbert_Wassenaar]

At the end of the script would do.

[#9]

For the other people I copy this one and changed the relevant fields.

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

Temp: 

Load 

min(OrderDatederDate) as minDate, 

max(OrderDate) as maxDate 

Resident Orders; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

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, 

  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; 

Understanding the Master Calendar - Qlik Sense and QlikView - YouTube

this Youtube video was helping too