Master Calendar date groups

[mattcorke]

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

[dplr-rn]

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]

Similar logic for term too.
Hope it helps

Typing on my phone
Dilip

[mattcorke]

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]

Share your expression plus a sample data.
Also the error

[mattcorke]

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]

This illustrates how it isn't quite working.