Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
mattcorke
Contributor II
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 (1)
6 Replies
dplr-rn
Partner - Master III
Partner - Master III

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
dplr-rn
Partner - Master III
Partner - Master III

Similar logic for term too.
Hope it helps

Typing on my phone
Dilip
mattcorke
Contributor II
Contributor II
Author

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?

dplr-rn
Partner - Master III
Partner - Master III

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

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
Contributor II
Contributor II
Author

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

This illustrates how it isn't quite working.