7 Replies Latest reply on Jul 26, 2018 2:17 PM by swaroop.gantela

# Percent Change in Population from Year to Year

Hey All,

I would like to calculate the percent change in population of each individual state from one year to the next. Year is being used as a quick filter and the state dimension is on columns so a percent difference table calculations isn't possible, or at least as far as I've tried.

I would like the user to be able to filter through the years and each state show a percent difference in population from the previous year. I have a feeling this needs to be a LOD calc, but right now I'm at a complete impasse as what that calc may be. Any and all help is much appreciated. Thanks.

-Reid

• ###### 1. Re: Percent Change in Population from Year to Year

Hi Reid,

Do you mean something like below and attached?

• ###### 2. Re: Percent Change in Population from Year to Year

Don,

Yes. But I'd prefer to only see one year at a time and not have a dual axis chart. If you go to the 'Rankings' dash you will see the view I'm trying to build. Top sheet has the totals for each metric (births, deaths etc.) and then the bottom sheet has the metric as a % of population. When a user selects the population metric in the top left parameter I would like to show the ranking of percent pop. change from the previous year. I hope this explains it all, let me know if it doesn't. Appreciate it.

-Reid

• ###### 3. Re: Percent Change in Population from Year to Year

Hi Reid,

Please see newly attached workbook (10.5 version).  There's really no other way to get this view without doing a dual-axis.  However, using a new calculation as a filter, I used LAST( ), this then works towards filtering just the current year in the view but still compares/evaluates to the previous year:

Thanks, Don

• ###### 4. Re: Percent Change in Population from Year to Year

Reid and Don,

One other method to try is using a table calculation of Lookup to get the percent difference:

( SUM([Population])-LOOKUP(SUM([Population]),-1) )

/

LOOKUP(SUM([Population]),-1)

The drawback of this method is that it typically requires the previous year's data to be on the view,

but this can be avoided by using another lookup as the year filter, as described here:

Filtering the View Without Filtering Underlying Data | Tableau Software

So the lookup year is: LOOKUP(ATTR([Year]),0)

But the further drawback is that this new Year Filter cannot be used across multiple sheets,

and so one will have to use the parameter method which you had already set up.

So the year filter would be: [YearLookup]=[Select Year (parameter)]

And so your other sheet would also need to have a similar filter to check if equal to parameter

selection. And so the residual issue of using a parameter is that it is not dynamic and will

need to be updated whenever you add a new of data.

One further issue is the sorting of the states from high to low.

This can be accomplished by putting the percent difference as a discrete pill to the left of state.

I also edited this pill in-shelf to put a negative sign in front to get it to sort descending.

OK, all those caveats aside, please see if there may be some use in the last two sheets of the

workbook v10.5 attached in the Forum thread.

• ###### 5. Re: Percent Change in Population from Year to Year

Hi Swaroop!

Thanks for the tips & tricks here!  It's always good to have additional options even if there are caveats to the solution. Good to see you back on the forums! Thx, Don

• ###### 6. Re: Percent Change in Population from Year to Year

Huge help you two. Thanks a bunch. For my purposes I'm going to go with Swaroop's version even with the extra leg work, but I really like the design/look of Don's. Cheers!

-Reid

• ###### 7. Re: Percent Change in Population from Year to Year

Reid,

Glad it worked out for you.

I too like the design of Don's giving both the current and the percent change.

All the best.