Hide columns in pivot table based on sum

[#1]

Hi,

I've encountered a problem with a pivot chart where I would like to hide columns (dimensions) that only contains null values or zero values (i.e only show columns where the total column sum > 0) Closest I've got is simply to suppress zero-values which works as I want it to for the columns, problem is it also hide the rows that are zero as well. Is per the table below, I would like to hide the column "After sales" since it only contain blank values but keep the Item2 row visible even though it would also sum 0.

		Stock profile	10		20
		Stock profile name	ten		twenty
Item	Description	Stock name	After sales	Main stock	Consignment
Item1	aa		-	1	0
Item2	bb		0	0	-
Item3	cc		-	0	1
Item..n	dd		0	1	0

Thank in advance

Finn

[marcus_sommer]

You could try it with a condition on this column like: sum(TOTAL [After sales]) and/or a calculated dimension like: aggr(if(sum([After sales])>0, Item), Item) and/or a condition within the expression itself like: if(sum([After sales])=0, null(), sum([After sales])) and using the suppress zero-option but not each view-combination will be (practically) possible.

- Marcus

[#3]

Hi Marcus and thanks for your response.

I don't think placing a condition on the "Stock name" Dimension will work since this will hide either all or none of the stock places (After sales, Main stock, Cons)

Neither do I think suppress zero-values will work regardless of the expression since I want the pivot to suppress zeroes on columns but now rows, and there is no such distinction in the chart options.

Your suggestion to add a calculated dimension got me thinking and I ended up making a calculated dimension that summarized the total "stock name" balance and display it pivoted "below" "stock name". By placing a condition on the summary where it returns null() if the sum is 0 I could use suppress if value is null on the dimension and almost achieve what I wanted. The only flaw is that it will still remove rows (items) with 0 stock balance where the column summary is 0 (null), though this is pretty rare.

[aapurva09]

Hi,

To achieve this, we can use set analysis in the expressions and remove null from the expression only. By doing this, the field values in the dimension where there is a null will automatically get removed.

The set could be like this:

=sum({<Stock={"=sum(Sales)>=0"}>}Stock)

Thanks