topic Re: How to write an expression that dynamicly identifies a year? in QlikView

How to write an expression that dynamicly identifies a year?

Anonymous — Wed, 07 Jun 2017 10:28:39 GMT

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

Re: How to write an expression that dynamicly identifies a year?

stabben23 — Wed, 07 Jun 2017 11:27:44 GMT

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.

Re: How to write an expression that dynamicly identifies a year?

sunny_talwar — Wed, 07 Jun 2017 11:33:44 GMT

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)

Re: How to write an expression that dynamicly identifies a year?

Anonymous — Wed, 07 Jun 2017 11:38:40 GMT

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

Re: How to write an expression that dynamicly identifies a year?

Anonymous — Wed, 07 Jun 2017 11:46:11 GMT

Both ways work!! Thanks a lot

Re: How to write an expression that dynamicly identifies a year?

sunny_talwar — Wed, 07 Jun 2017 11:49:36 GMT

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

Best,

Sunny

Re: How to write an expression that dynamicly identifies a year?

Anonymous — Wed, 07 Jun 2017 11:53:31 GMT

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

Re: How to write an expression that dynamicly identifies a year?

sunny_talwar — Wed, 07 Jun 2017 11:56:08 GMT

No problem