CODEWITHSUNDEEP
filteringtableau-api
Dave van Brecht
Dave van Brecht

Reputation: 514

Filtering for all periods based on one period

I have a tableau question relating filtering. I want to filter for values above a particular point for multiple IDs and periods. Suppose:

ID   Date    Balance
1    2014    100
1    2015    100
1    2016    90
1    2017    80
2    2014    120
2    2015    150
2    2016    100
2    2017    90
3    2014    200
3    2015    250
3    2016    150
3    2017    50

Suppose that my filtervalue is 130. Then tableau returns all balances above 130, obiously. However, I want to delete the loanIDs in all periods if the balance in the first period (2014) is above 130. Thus I would like to obtain the following:

ID   Date    Balance
1    2014    100
1    2015    100
1    2016    90
1    2017    80
2    2014    120
2    2015    150
2    2016    100
2    2017    90

As you can see the value 150 is still in there but this balance was in 2015. Any help is appreciated! Thank you

Upvotes: 0

Views: 130

Answers (1)

Inox
Inox

Reputation: 2275

Well, sometimes (most times when the solution is not obvious, actually) you need to be creative to achieve what you need. The solution I'll present is not pretty, it's not straight forward, but it works.

When I think of filters that are not obvious (like, take any value over X), I like to think of markers, calculated fields that has only one purpose, raise a flag.

Basically you want this marker to raise a flag if the balance of 2014 is over a value (I'll assume you have a parameter called 'filtervalue'). So you create the marker (will call it 'marker'):

IF YEAR([Date]) = 2014 AND [Balance] > [filtervalue]
THEN 1
ELSE 0
END

I assumed [Date] is in actually a date or datetime format. If it is just the year, in numeric format, drop the 'YEAR()'

Now you have a marker, it will be 1 if the balance in year 2014 is over filtervalue, and 0 in all other cases (the year is not 2014, or is under filtervalue in 2014).

Now, drag ID to the filters, and go to Condition. That's where the magic happens. Select By field, [marker], Maximum, = 0. For every ID, it will search every value of [marker] for that ID and select the maximum, and will keep only the ones that are equal to zero. Meaning, if there is a marker = 1 anywhere, the ID will be filtered out

Hope it helps

Upvotes: 1

Related Questions