5 Replies Latest reply on Nov 4, 2018 10:58 PM by Cédric Tran

# Dynamic Sets for Top "X

Hi,

I am working on a project for class that deals with rates. I have plotted countries and their rates across years but I am trying to create a dynamic set that would allow me to place the set into color to highlight the top 5 countries based on the most recent year that is visible on the time series. I have attached the workbook as well as an annotation for more details. I was trying to add a formula in the set such as YEAR(Year) = 2015 but it kept giving me calculation issues.

Thank you,

Sam

• ###### 1. Re: Dynamic Sets for Top "X

I haven't upgraded to 2018.3 yet. Please downgrade it to 2018.1 or below so that I can check.

• ###### 2. Re: Dynamic Sets for Top "X

Hi,

To make the Top N dynamic,

Create Integer Parameter, Assign this Parameter to your TOP Set.

Cedric

• ###### 3. Re: Dynamic Sets for Top "X

Hello Ombir,

I have uploaded the workbook as 10.5 in my original post.

Also, I was able to do a quick work around but it is not dynamic.

I created the following calculation and placed it in the condition for the Set: If YEAR(Year) = 2015 then [Value] END.

However, even when I filter out the year 2015, the Set still contains the results for the year 2015. I want it so that if 2014 is the most recent year, then the Set should dynamically adjust the calculation to see what the top rates were based on 2014 and so on.

Thanks,

Sam

• ###### 4. Re: Dynamic Sets for Top "X

Hi Cedric,

Would you be able to go into a little more detail?

Sam

• ###### 5. Re: Dynamic Sets for Top "X

Hi,

I did not understand that you want the year to be dynamic ! :-)

So if you want to see the rank of 2014 when 2014 is selected, make a simple filter.

If you want to Full Rank until 2014 if 2014 is selected, here is how I would do:

1. Create a Integer Parameter with a list of Year: 2018, 2017, 2016... Name it "Selected Year"

2. Create a Set for your Date dimension, write the Formula on the Condition Tab YEAR([Date]) < [Selected Year].. Name it "Year Set"

3. Put "Year Set" in the Filter

If you are creating a rank on the full range of data, let the filter as it is. Otherwise, if you are building it with a LOD, you may have to add the Year Set Filter to the Context.

Cedric