Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
mattcorke
New Contributor II

Master Calendar date groups

Hi there,

I am using this master calendar script:

AssessmentMasterCalendar:
Load
CalendarDate as AssessmentDate,
Day(CalendarDate) as Assessment.Day,
Year(CalendarDate) as Assessment.Year,
Month(CalendarDate) as Assessment.Month,
Week(CalendarDate) as Assessment.Week,
date(MonthStart(CalendarDate), 'MMM-YYYY') as Assessment.MonthYear,

;

Load
Date(MinDate + IterNo()) as CalendarDate
While MinDate+IterNo() <= MaxDate
;

Load
num(MIN (fieldvalue('AssessmentDate', RecNo()))) AS MinDate,
num(Max (fieldvalue('AssessmentDate', RecNo()))) AS MaxDate
Autogenerate FieldValueCount('AssessmentDate')
;

I would like to add the following groups, but have no idea how to do it! Any help would be awesome.

1. Academic year - academic years fall between August 1st and July 31st. Current year is 2018-19. Any date between 1st August 2017 and and 31st July 2018 would be 2017-18... and so on.

2. Term - 1st term is between 1st August to 31st December; 2nd term between 1st January and March 31st; 3rd term between 1st April and 31st July.

 

Many thanks in advance,

Matt

Labels (2)
6 Replies
dilipranjith
Honored Contributor

Re: Master Calendar date groups

I would use month function to acheive this
If (month(calendardate)>=8,year(calendardate)&'-'&year(calendardate)+1, if(month(calendardate)<=7,year(calendardate)-1&'-'&year(calendardate))) as acamedicyear
dilipranjith
Honored Contributor

Re: Master Calendar date groups

Similar logic for term too.
Hope it helps

Typing on my phone
Dilip
mattcorke
New Contributor II

Re: Master Calendar date groups

Thank you! It is close to working.

For assessment dates in Jan - July the Academic Year value is being created.

However, for any assessment in August - December it isn't. I've played around with the if statement a bit, but can't get it to work.

Any ideas?

dilipranjith
Honored Contributor

Re: Master Calendar date groups

Share your expression plus a sample data.
Also the error
mattcorke
New Contributor II

Re: Master Calendar date groups

Here is the expression in the Calendar:

AssessmentMasterCalendar:
Load
CalendarDate as AssessmentDate,
Day(CalendarDate) as Assessment.Day,
Year(CalendarDate) as Assessment.Year,
Month(CalendarDate) as Assessment.Month,
Week(CalendarDate) as Assessment.Week,
date(MonthStart(CalendarDate), 'MMM-YYYY') as Assessment.MonthYear,
InYearToDate(CalendarDate, '$(vToday)', 0) * -1 as "Current Academic Year",
InYearToDate(CalendarDate, '$(vToday)', -1) * -1 as "Last Academic Year",
If (month(CalendarDate)>=8, year(CalendarDate)&'-'& year(CalendarDate)+1, if(month (CalendarDate)<=7, year(CalendarDate)-1&'-'&year(CalendarDate))) as "Academic Year",
;

Load
Date(MinDate + IterNo()) as CalendarDate
While MinDate+IterNo() <= MaxDate
;

Load
num(MIN (fieldvalue('AssessmentDate', RecNo()))) AS MinDate,
num(Max (fieldvalue('AssessmentDate', RecNo()))) AS MaxDate
Autogenerate FieldValueCount('AssessmentDate')
;

 

Here is the AssessmentDate field in the fact table:

AssessmentData:
LOAD
StudentID,
"Form or Set" as Assessment.FormSet,
"Year group" as YearGroup,
Subject,
"Assessment date" As AssessmentDate,
"Assessment name",
"Assessment type",
"% achieved",
"Exam grade",
TeacherID
FROM [lib://Assessment data/Assessment data*.xlsx]
(ooxml, embedded labels, table is [Pupil assessment data]);

 

And attached is the Excel sheet which stores the data - I've kept the dates plus some random assessment scores.

Thanks for your help.

Matt

mattcorke
New Contributor II

Re: Master Calendar date groups

2018-12-01_20-23-03.jpg

This illustrates how it isn't quite working.