Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to write an expression that dynamicly identifies a year?

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

1 Solution

Accepted Solutions
stabben23
Partner - Master
Partner - Master

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.

View solution in original post

7 Replies
stabben23
Partner - Master
Partner - Master

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
Not applicable
Author

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

Anonymous
Not applicable
Author

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
Not applicable
Author

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

sunny_talwar

No problem