How to write an expression that dynamicly identifies a year?

[Anonymous]

I have a dataset that shows the nationality among foreign students.
I have data for the years 2013-2016.

I want to make a straight table with nationality as a dimension and show the number of students in each category of nationality for every year, so that I end up with 4 columns with numbers.

I have defined a vMaxAargang variable (max year=2016) and a vMinAargang (min year=2013).

In the expressions tab I write this for the year 2016:

=count({<[Hjemland2]={'Udenlandsk'}, [Type_stat]={'Heltid'}, [type_stat2]={'Udl Heltid'}, [over_udd]={'Bachelor', 'Kandidat'}, [aargang]={$(vMaxAargang)} >}PERSON_ID) 

This gives me the data that I want for 2016.

My problem is to get the data for 2015, 2014 and 2013.

I have tried to modify the above code to go 1(,2,3) years back – without luck:

=count({<[Hjemland2]={'Udenlandsk'}, [Type_stat]={'Heltid'}, [type_stat2]={'Udl Heltid'}, [over_udd]={'Bachelor', 'Kandidat'}, [aargang]={$(vMaxAargang-1)} >}PERSON_ID) 

=count({<[Hjemland2]={'Udenlandsk'}, [Type_stat]={'Heltid'}, [type_stat2]={'Udl Heltid'}, [over_udd]={'Bachelor', 'Kandidat'}, [aargang]={$(vMaxAargang)-1} >}PERSON_ID) 

How do I tell QV to go X years back in the script without hardcoding?

KIND REGARDS

Maria

[stabben23 · Accepted solution]

Hi Maria,

why not use Year as a Dimension in a pivot where you drag and drop the Year Dimension as a header, like this.

[sunny_talwar]

May be try this

=Count({<[Hjemland2] = {'Udenlandsk'}, [Type_stat] = {'Heltid'}, [type_stat2] = {'Udl Heltid'}, [over_udd]={'Bachelor', 'Kandidat'}, [aargang] = {$(=$(vMaxAargang)-1)}>}PERSON_ID)

or this

=Count({<[Hjemland2] = {'Udenlandsk'}, [Type_stat] = {'Heltid'}, [type_stat2] = {'Udl Heltid'}, [over_udd]={'Bachelor', 'Kandidat'}, [aargang] = {$(=vMaxAargang-1)}>}PERSON_ID)

[Anonymous]

Ahh yes, a little easier than my suggestion. Thanks.

[Anonymous]

Both ways work!! Thanks a lot

[sunny_talwar]

Great, I suggest marking stabben23‌ response as correct instead of marking your own response as the correct response

Best,

Sunny

[Anonymous]

Done - thanks for heads up. I didn't realize I had done that

[sunny_talwar]

No problem