Can I do an OVER custom expression without displaying both data columns

[Li Howe]

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

[Khushboo Rabadia]

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

[Li Howe]

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!