topic Re: Only show specified year selection in QlikView

Only show specified year selection

Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone,

I have a year coloum from 2000 to 2016. Now I have two slider objects, where I can select the desired start year and another one for the desired end year.

E.g. I only wanna illustrate years 2010 to 2014 in my bar chart.

The variable for the start year is called "yearlow" and for the end year "yearhigh".

Dimensions: company name and year

Expression is normally something like this: =sum({<company=$::company>}[net profit])

----------

I have two ideas how to solve it:

1. instead of the year dimension a customized dimension where I include the two year variables, but how?

2. Include the year range via set analysis in the expression

Your support is much appreciated guys!

Regards!

Re: Only show specified year selection

Gysbert_Wassenaar — Wed, 22 Jun 2016 16:05:41 GMT

A set analysis expression sounds like a good idea: sum({<year={'>=$(yearlow)<=$(yearhigh)'}, company=$::company>}[net profit])

Re: Only show specified year selection

Wed, 22 Jun 2016 16:46:58 GMT

Thank you very much. Unfortunately, it doesn't work yet, but I assume there's only a syntax error.

I now use a scatter chart:

x: =sum($(variableforalternatestate1)+$(variableforalternatestate2)[$(fieldx)])

y: =sum($(variableforalternatestate1)+$(variableforalternatestate2)[$(fieldy)])

z: =sum($(variableforalternatestate1)+$(variableforalternatestate2)[$(fieldz)])

variableforalternatestate1

=if([Button]=1,'{<year={'>=$(yearlow)<=$(yearhigh)'}, company=alternatstate1::company}>',

if([Button]=2,'{<year={'>=$(yearlow)<=$(yearhigh)'}, company=alternatstate2::company}>',

if([Button]=3,'{<year={'>=$(yearlow)<=$(yearhigh)'}, company=alternatstate3::company}>',

if([Button]=4,'{<year={'>=$(yearlow)<=$(yearhigh)'}, company=alternatstate4::company}>',

if([Button]=5,'{<year={'>=$(yearlow)<=$(yearhigh)'}, company=alternatstate5::company}>')))))

variableforalternatestate2 is similar to this

fieldx ,fieldy and fieldz definitely work.

Again, thanks for your help!

Re: Only show specified year selection

johnw — Wed, 22 Jun 2016 19:48:08 GMT

Rather than variables and set analysis, consider incorporating it into your data model. You wouldn't hardcode the years like this, just giving you the idea of what the data would need to look like.

yearlow:
yearlow,year
2000,2000
2000,2001
2000,2002
...
2000,2016
2001,2001
2001,2002
...
2015,2015
2015,2016
2016,2016

yearhigh:
LOAD
year as yearhigh
,yearlow as year
RESIDENT yearlow
;

Then go ahead and keep year as your dimension, and go ahead and keep the same expression. You just let them select yearlow and yearhigh like any other value. Maybe a calendar object.

Re: Only show specified year selection

Thu, 23 Jun 2016 11:24:09 GMT

Thank you John, but I actually figured out how to use the above approach.

Actually, it was important to split the argument into two parts, as the ' ' caused the syntax issue.

=if([Button]=1,'{<year={'>=$(yearlow)<=$(yearhigh)'}, company=alternatstate1::company}>',

So I made another variable for the underlined part.. let's call it yearselect.

Finally, my expression looks like this: =sum({<year={$(yearselect)},$(variableforalternatestate1)>+<year={$(yearselect)},$(variableforalternatestate2)>}[$(fieldx)])

Many thanks to both of you! Much appreciated!