4 Replies Latest reply on Sep 26, 2018 11:06 AM by Naveen B

# How to only show relevant dimensions that cover 4 consecutive years?

Hope you can help me with below question:

In below table I have 6 city-to-city lanes with their annual volumes. As you can see, in some occasions the values of a certain is left empty.

I want to change the view to the lanes that did only had a value in each year. In this occasion that would be only lane 2 and lane 6.

Did someone had to do this exercise before? and how did you solve this?

Thank you for helping out!

Mark

• ###### 1. Re: How to only show relevant dimensions that cover 4 consecutive years?

Hi Mark,

Create a calculation like below

Formula

Only City having all values:

{Fixed [City-city]:MIN(

{Fixed [City-city],[Year]:IFNULL(MIN([Volume]),0)})}=0

and Add it to Filter and Select False

You will get the final O/P

Hope this helps

BR,

NB

• ###### 2. Re: How to only show relevant dimensions that cover 4 consecutive years?

Thanks Naveen for your prompt repsonse! Works perfect!

• ###### 3. Re: How to only show relevant dimensions that cover 4 consecutive years?

Naveen B

I thought it would solve my problem. However, I just noted there is a difference in the source data to the example I have provided and the working file I have.

In the example I mentioned the zero values lanes as well. Whereas in my data source these are not available. Therefore, I guess it would not work in my working file with the actual real live data.

See attached the new example with the new workbook. Hope you can help solving this. Sorry for the inconvenience.

• ###### 4. Re: How to only show relevant dimensions that cover 4 consecutive years?

Hi Mark,

Based on your latest workbook solution is very easy

Step 1: Create a calculated field like below

Step 2: Drag to Filter:

Final O/P

Hope this helps