Selective dimension show

[jduluc12]

Hi,

My data set looks like this.

Customer	Item	Amount
abc	Item1	20
xyz	item1	30
abc	item2	50
abc	item3	60

now the pivot chart should look like this

Customer	Item	Amount
abc	item2	50
	item3	60
xyz	item1	30

So what is happening here:

We are not considering item1 for the customer abc.

I can write "if" condition in Item dimension and the Amount expression like

if (Customer='abc' and Item='item1', '', Item)

and

if (Customer='abc' and Item='item1', '', sum(Amount))

but it shows up like this

Customer	Item	Amount
abc
	item2	50
	item3	60
xyz	item1	30

it is leaving a blank row for abc.

I want to avoid it.

Any idea on how to achieve it ?

Thanks,

[datanibbler]

Hi Jean,

I think you have just mixed up the THEN and ELSE parameters in your IF clauses, would that be possible?

Can't think of anything else right now.

Best regards,

DataNibbler

P.S.: Ah, no, I get it now - you want to display Items 2 and 3, but not 1 for customer abc - then you have to re-formulate your IF_conditions - such as it is, you have both "on the same level", connected by AND.

Is ITEM a dimension or an expression in your chart? if it's a dimension, there is a checkbox "suppress ZERO values" or so, try that.

[sunny_talwar · Accepted solution]

How about just doing

Dimension

Customer

Item

Expression

=Sum({$-<Customer = {'abc'}, Item = {'Item1'}>}Amount)

[prma7799]

Like this ?

[kamal_sanguri]

you have item & Item.. make them same

Edir:

They are in different case (QV is case sensetive)

[kamal_sanguri]

Use NULL() instead of ''. It is working for me.

if (Customer='abc' and Item='item1', NULL(), Item)