How to hide some data in Pivot table

[#1]

Hi All,

I have the following table:

Name, Revenue

A,100

A(1),100

B,200

B(1),200

C,300

As you can see the some Name has relationship, like A and A(1) and they shared the same revenue.

What I want to is if the name has relationship, then show the Revenue in the second one, if not show it directly.

For the above sample, the result is in Pivot table and Use Name as dimension

Name, Revenue

A, -

A(1), 100

B,-

B(1),-

C,300

How to achieve it?

Thanks.

[#2]

Hi,

You could try with:

if(Only(Name)<>SubField(Below(Only(Name)), '(', 1), sum(Revenue))

PFA

[adnan_rafiq]

Hi Isaac,

if you are not using duplicate rows any where else you can just exclude them when you reload data.

by using order by and left function like or you can use distinct function with left function

Load distinct left( Name,index(Name,'(')) as Name ,

Revenue

From file

if name has

[#4]

Hi Iosu, thanks for your expression, it works well. However, if I add one more dimension into the pivot table,

the Below function will find the wrong Name, how to solve it? And Because I change the Pivot table into Straight table. And I attached the QVW.

[tresesco]

Use total keyword like attached sample.

[#6]

Hi Isaac,

Just add the TOTAL clause to the 'below' expression:

if(Only(Name)<>SubField(Below(TOTAL Only(Name)), '(', 1), sum(Revenue))

It works this way

[#7]

Yes... it works very well, could you tell me, what's the meaning of that total keyword in this expression?

[tresesco]

TOTAL is used to disregard dimension. For better understanding, please go through the attached qvw.