Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
mariasdu
Contributor

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
Honored Contributor

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

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.

7 Replies
stabben23
Honored Contributor

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

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?

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)

mariasdu
Contributor

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

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

mariasdu
Contributor

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

Both ways work!! Thanks a lot

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

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

Best,

Sunny

mariasdu
Contributor

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

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

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

No problem

Community Browser