How to create Semester from date field in Qlik Sense?

[qlik_user3]

Hi,

I have a column with dates (DD/MM/YYYY). I need to create a filter panel where I can select a semester.
For example, if I have four dates (01/02/2017, 03/05/2017, 01/01/2018, 01/12/2018), I would show, on my filter panel, 

2017-1Semester

2018-1Semester

2018-2Semester

I'm a new user, can you help me, please?

Thanks

[micheledenardi · Accepted solution]

When you have to work with dates (and periods in general) the best approach is always by creating a master calendar where you can create all dimensions you need:

FactTable:
Load * Inline [
Sales,Date
10,01/02/2017
21,03/05/2017
8,01/01/2018
4,01/12/2018];

MasterCalendar: 
Load 
  TempDate 		  As Date,
  Year(TempDate) 	  As [Year],
  num(Month(TempDate),00) As [Month],
  Year(TempDate)&'-' & Ceil(Month(TempDate) / 6)&' Semester' As [Semester],
  Year(TempDate)&'-' &Ceil(Month(TempDate) / 3)&' Quarter'   As [Quarter];
//=== Generate a temp table of dates === 
LOAD 
 	date(mindate + IterNo()) 	AS TempDate,
 	maxdate // Used in InYearToDate() above, but not kept 
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
	min(FieldValue('Date', recno()))-1 	as mindate,
	max(FieldValue('Date', recno())) 	as maxdate
AUTOGENERATE FieldValueCount('Date');

and that's the result:

for more documentation check here:

QlikView Master Calendar 

Understanding the Master Calendar - Qlik Sense and QlikView 

[qlik_user3]

Ciao Michele, grazie per il tuo aiuto! 

Per risolvere il mio problema c'è solo un problema riguardante il filtro, vedo correttamente la divisione per semestre, adattata ai miei anni, però cliccando una delle opzioni del filtro non si applica alla tabella sottostante. E' come se il filtro non funzionasse. Sapresti dirmi che errore commetto? Ti allego una foto del filtro e una foto del codice.

Grazie anticipatamente

[micheledenardi · Accepted solution]

That happens because your fact table is not linked to you master calendar.

You have to create the key between these two tables by renaming TempDate in MasterCalendar as [DATA PUBBLICAZIONE].

Like this:

MasterCalendar: 
Load 
  TempDate 		  As [DATA PUBBLICAZIONE],
  Year(TempDate) 	  As [Year],
  num(Month(TempDate),00) As [Month],
  Year(TempDate)&'-' & Ceil(Month(TempDate) / 6)&' Semester' As [Semester],
  Year(TempDate)&'-' &Ceil(Month(TempDate) / 3)&' Quarter'   As [Quarter];
//=== Generate a temp table of dates === 
LOAD 
 	date(mindate + IterNo()) 	AS TempDate,
 	maxdate // Used in InYearToDate() above, but not kept 
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
	min(FieldValue('DATA PUBBLICAZIONE', recno()))-1 	as mindate,
	max(FieldValue('DATA PUBBLICAZIONE', recno())) 	as maxdate
AUTOGENERATE FieldValueCount('DATA PUBBLICAZIONE');

Check qlik data model viewer to have an overview about your tables structure and keys.

[qlik_user3]

Thank you so much.

All is clear and it works.

Have a good day