CODEWITHSUNDEEP
datetableau-api
Rammb
Rammb

Reputation: 47

Selecting values between dates in Tableau

I started playing with Tableau. My data looks like below.

My Data

I want to create Calculated Field that divides my viz by color. I've got problem with dealing with dates. As you can see variable Quarter consists quarters of each year. What I want is to get rid of those gaps. I know my calculated field is done wrong but I tried so many different options and none of them worked. Basically I want each President starts (for example Donald Trump) his president's seat at 01.01.2017 and finishes it at 31.12.2020.

How should I write it to actually work?

IF YEAR([Quarter]) >= 1981 and YEAR([Quarter]) <= 1989 then 'Ronald Reagan' 
ELSEIF YEAR([Quarter]) >= 1989 and YEAR([Quarter]) <= 1993 then 'George H. W. Bush' 
ELSEIF YEAR([Quarter]) >= 1993 and YEAR([Quarter]) <= 2001 then 'Bill Clinton' 
ELSEIF YEAR([Quarter]) >= 2001 and YEAR([Quarter]) <= 2009 then 'George W. Bush' 
ELSEIF YEAR([Quarter]) >= 2009 and YEAR([Quarter]) <= 2017 then 'Barack Obama' 
ELSEIF YEAR([Quarter]) >= 2017 and YEAR([Quarter]) <= 2021 then 'Donald Trump' 
END

Viz

Upvotes: 0

Views: 248

Answers (1)

Alex Blakemore
Alex Blakemore

Reputation: 11919

The breaks you see in the line aren’t caused by your calculation, but here are two suggestions nonetheless - and some other tips.

Compare against complete dates, either using date literals (enclosed in # signs) or calling a function like MakeDate(). And you can simplify and speed up your calc by taking advantage of the knowledge that prior tests failed - I.e. to not repeat those tests, as shown below.

if [Quarter] < #01/20/1981# then ‘Historical’
elseif [Quarter] < #01/20/1989# then 'Ronald Reagan'
elseif [Quarter] < #01/20/1993# then 'George H. W. Bush'
elseif [Quarter] < #01/20/2001# then 'Bill Clinton'
elseif [Quarter] < #01/20/2009# then 'George W. Bush'' 
elseif [Quarter] < #01/20/2017# then 'Barack Obama' 
elseif [Quarter] < #01/20/2021# then 'Donald Trump'
else ‘Joe Biden’
end

@AniGoyal’s suggestion to make a separate table and use a join is a good idea too, but the join calc won’t look like the one above - hint - it would have 2 tests

As to the gaps between marks ...

After you place this field on the color shelf, try changing its role from dimension to attribute (and back) to see which view you prefer. You could also directly drag the tiles in your color legend to sort them in chronological order.

Upvotes: 3

Related Questions