Only show specified year selection

[#1]

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!

[Gysbert_Wassenaar · Accepted solution]

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

[#3]

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!

[johnw]

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.

[#5]

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!