CODEWITHSUNDEEP
spotfire
Jeremy Hluchan
Jeremy Hluchan

Reputation: 13

How to hide grand total of 0 on cross table

I have a cross table that has a switch where I have identified a unique identifier to be true or false. The only problem is when I select true, every single item is selected even though the value is at 0. The reason why is because every single item would have qualified as true at some point in time but have since cleared to being 0. So, I am trying to input a function stating that if a line has a grand total of 0, hide. Anyone know how to do this?

I created a calculated column stating: CASE WHEN [ACCOUNT] = 5000 THEN [AMOUNT] ELSE 0 END. This creates a unique identifier, but only halfway. Every item would have had data in 5000 at its inception, but eventually all gets cleared out. I created a cross table to show what has data in account 5000 and what does not. I built a switch to state: if(${prov}=[prov],true,false)end. And.... If(sum([amount]) over ([item])<>0.00,true,false). The goal is to make the switch to where a false shows balances of items that do not have data in account 5000. That works. However, when selecting true, it includes every item since all items were in account 5000 at some point in time, but are now at a 0 value. I want to hide these 0 values. Is this possible?

End goal: I have a cross table with a grand total set for the rows. Some rows have 0 for the grand total. I want to hide those 0's.

I am unable to give screenshots or real data due to what it is.

This is in spotfire

Upvotes: 0

Views: 1531

Answers (1)

clesiemo3
clesiemo3

Reputation: 1129

If you're simply looking to hide the value from your cross table you shouldn't need anything more than the "Show/Hide Items" menu.

You can set it to Hide Equal to 0.00: Sum(amount) and be good to go.

One thing to keep in mind with calculated columns vs. the values that appear in a cross table is that the cross table values are subject to filters you apply while your calculated column is across all your data. This could cause you to think something has a Sum() == 0 when really it's just the subset of your data that does.

Upvotes: 2

Related Questions