Set Analysis Previous term / semester

[Jonathan_Alm]

Hi,

we have school grades that are connected to a semester or term.

The semesters are listed as:

20161

20152

20151

20142

20141

20132

20131

20161 = Spring 2016

20152 = Autumn 2015

20151 = Spring 2015

20142 = Autumn 2015

20141 = Spring 2014

20132 = Autumn 2014

20131 = Spring 2013

For this year, the school year started with 20152 (which is Autumn 2015) Next semester will be 20161 (which is Spring 2016).

I want to be able to perform a set analysis that is similar to:

Avg({

$<markPeriod={">=$(=max(markPeriod)-1)"}

>}Grade)

The problem is that max(markPeriod) = 20161,

but

max(markPeriod)-1) = 20160

I want it to be 20152

Does anyone have an idea on how to solve this problem?

Kind regards, Jonathan

[Gysbert_Wassenaar · Accepted solution]

Create an additional counter field that increases by one for each semester value:

MySemesters:

LOAD Semester, Autonumber(Semester, 'Semester') as _SemesterSerial

Resident SomeTable

Order By Semester;

You can then use the _SemesterSerial in your set analysis expression with the max function.

Note, if your source data is already ordered by Semester then you don't need a new table like MySemesters, but can create the _SemesterSerial directly in the load statement that loads the data from the source.

[Jonathan_Alm]

Thanks Gysbert, it works like a charm.

I went for your second suggestion. The semesters were already sorted in the Extract-part of my script so I could easily implement the autonumber in the transform-part.

Kind regards, Jonathan