Prevent date fields being linked

[mattcorke]

Hi everyone,

I'm pretty new to this so apologies if I've missed something simple.

I have concatenated two tables with assessment and report data for students. The assessment data and report data is given by teachers for specific subjects.

The script looks like this:

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

;
Concatenate (Assessment_and_Report_Data)
LOAD
StudentID,
"Year group",
"Report Cycle" as [Report date],
Subject,
"Form or Set",
Target,
Attainment as [Attainment grade],
Effort,
EffortNo,
TeacherID
FROM [lib://Report data/Training - Pupil report data.xlsx]
(ooxml, embedded labels, table is [Pupil report data]);

I have kept the date fields separated so that the data can be searched separately e.g. selected several months of assessment data and compare to attainment data for a single month. 

Each date has a master calendar attached. The script for this is below:

AssessmentMasterCalendar:
Load
CalendarDate as [Assessment date],
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",

;

Load
Date(AssMinDate + IterNo()) as CalendarDate
While AssMinDate+IterNo() <= AssMaxDate
;

Load
num(MIN (fieldvalue('Assessment date', RecNo()))) AS AssMinDate,
num(Max (fieldvalue('Assessment date', RecNo()))) AS AssMaxDate
Autogenerate FieldValueCount('Assessment date')
;

ReportsMasterCalendar:
Load
ReportCalendarDate as [Report date],
Year(ReportCalendarDate) as Report.Year,
Month(ReportCalendarDate) as Report.Month,
date(MonthStart(ReportCalendarDate), 'MMM-YYYY') as Report.MonthYear,
//If (month (ReportCalendarDate)<=7, year(ReportCalendarDate)-1&'-'&year(ReportCalendarDate), if (month(ReportCalendarDate)>=8, year(ReportCalendarDate)&'-'& year(ReportCalendarDate)+1)) as "Report Academic Year",

;

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

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

To test that this is working I have a few bar charts - assessment % by month by teacher, report data by month by teacher. And a scatter graph with pupil as dimension (linked from studentID) and attainment grade and assessment % as measures. 

The problem - when I select a month on either of the bar charts, the other goes blank and the scatter graph contains undefined values. 

I did have a link table to connect these tables rather than concatenate, but I had problems making this work too. Any help greatly appreciated - I've tried all I know (granted, it's not that much!)

Thank you!

Matt

[Anil_Babu_Samineni]

I would suggest you to maintain Only one Date field and use Bridge table / Link table concept to link that date field with Master calendar.

[mattcorke]

Would I then be able to apply date filters independently of one another?

I think the problem is that there could be a row with report data for a student with a date, then separate rows with assessment data. There are no rows that contain both report and assessment data, and therefore when a date is selected for reports no assessment data is returned and vice versa.

Thanks for your help.

Matt

[timpoismans]

You might want to take a look at the following: Canonical Date.  I think it might solve your issue.

Regards,

Tim

[mattcorke]

Thanks. I have got to this point now... A common calendar linked to the LinkTable which has a DateType field. When I select a month using the "Common.MonthYear" field it doesn't work properly - the dates shown for report data and assessment data change but incorrectly.

There are two other calendars for reports and assessments. Neither of these work properly - when I select a date from one calendar, it would modify the other as well.

Totally stumped. Would filtering the filter to only show assessment / report dates help? If so, how would I go about doing that?

Thanks for the help.

Matt

Data model

[timpoismans · Accepted solution]

Hi Matt

What's your final goal? What do you want to do exactly?

About your current issues:

If you make selections in your CommonCalendar and the other Calendars change, but not correctly, there is most likely an issue with your link field (%LinkTableKey). 
How does it behave differently than expected?

Making a selection in one Calendar influences the other because an indirect link exists between the tables. Selecting an assassment.month will limit the datamodel down to a certain amount of %LinkTableKey values. These in turn are linked to Report_Data_Link and thus to ReportsMasterCalendar.

[mattcorke]

Ah, that makes sense, thanks.

I've taken out the [Form or Set] and [ Year group]  from the link key and it is working now. Since they are different from year to year it controlled what dates were displayed in the assessment and report data. Without them it all looks good!

Thanks

Matt