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.
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:
State Dem Version_4b_v10.5.twbx 396.0 KB
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) )
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:
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.
276839lookup.twbx 407.3 KB
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
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!
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.