Li Howe Posted January 23, 2019 Share Posted January 23, 2019 I'm struggling with this and not sure if the simple answer is the only answer, or if there's a niftier way to do it that might be more scalable. I want to have a cross table with two columns: the total number of customers in the period, and the % change from the same period in the previous year. (I also have a requirement to do this in a graphical table, which is another side of pain since it has no X axis). Obviously, if I have time on the column axis, I can use an OVER statement or the % Different Year on Year function. But that means displaying at the very least the previous year's numbers as well, which my users don't want to see - they only need to see whether it's increased or decreased. I've tried limiting the data, but of course, as soon as I limit the data to only the current period, it filters out the previous year, so the % difference becomes blank. Does anyone know of there any way to use an OVER ParallelPeriod / NavigatePeriod etc, but only display the newest period, not the one being compared to My solution at the moment is to to a custom expression of (total customers in 2018) - (total customers in 2017) / (total customers in 2017), but using a helper column of DenseRank(date column stuff) to replace the actual dates with a 'period recency' that always stays the same as we move forward in time. It's an okay solution but I can't help feeling I've over-engineered it! Can anyone offer any other experiences Link to comment Share on other sites More sharing options...
Khushboo Rabadia Posted January 28, 2019 Share Posted January 28, 2019 Custom expression will work if you remove Year column from Axes and use in custom expression in the form of conditional statement If you do not want to remove previous period, you can hide it using Show/Hide Items in cross table using custom expression like : First(case when [Year]=Max([Year]) then 1 else 0 end) and show items with value equal to 1 This will show only current period data and hide the rest. It will not limit the data so you can still calculate the difference Link to comment Share on other sites More sharing options...
Li Howe Posted January 31, 2019 Author Share Posted January 31, 2019 Thank you Khushboo, I haven't made much use of the Show/Hide before in my Spotfire career but that's a great way to do it. The custom statement definitely works but I think I'll make use of the Show/Hide in future! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now