How to add SEGMENT ~= [Related Coy] into sum(if(L5M='-1',sales/1000)) ?

[paulyeo11]

HI ALL

I want to remove row , when SEGMENT field = Related Coy.

So i need to insert SEGMENT ~= [Related Coy] into below expression :-

sum(if(L5M='-1',sales/1000))

I imagine the expression should be :-

sum(if(L5M='-1',SEGMENT ~= [Related Coy],sales/1000))

But it cannot work.

Paul

[paulyeo11]

my qvw

[sunny_talwar · Accepted solution]

You can do this using a calculated dimension such as this:

=If(SEGMENT <> 'Related Coy', SEGMENT)

Or use set analysis in all your expressions

{<SEGMENT -= {'Related Coy'}>}

In the attached sample I have used the calculated dimension approach

[sunny_talwar]

Set Analysis approach sample is attached now

[paulyeo11]

Hi Sunny

Also thank you very much for give me SET analysis expression.

My QS chart 1, the expression :-

IF((sum(days3) + sum(days6) + sum(days9) + sum(above12)+sum(curren))>(sum(if(L5M,sales))),

(sum(if(L5M,sales))),0)

IF((sum(days3) + sum(days6) + sum(days9) + sum(above12)+sum(curren))>(sum(if(L5M,sales))),

(sum(days3) + sum(days6) + sum(days9) + sum(above12)+sum(curren)),0)

May i know how to add condition for :-

SEGMENT -= {'Related Coy'}

Paul

[sunny_talwar]

May be like this:

If((Sum({<SEGMENT -= {'Related Coy'}>} days3) + Sum({<SEGMENT -= {'Related Coy'}>} days6) + Sum({<SEGMENT -= {'Related Coy'}>} days9) + Sum({<SEGMENT -= {'Related Coy'}>} above12) + Sum({<SEGMENT -= {'Related Coy'}>} curren)) > (Sum({<SEGMENT -= {'Related Coy'}>} if(L5M,sales))),

(Sum({<SEGMENT -= {'Related Coy'}>} if(L5M,sales))),0)

If((Sum({<SEGMENT -= {'Related Coy'}>} days3) + Sum({<SEGMENT -= {'Related Coy'}>} days6) + Sum({<SEGMENT -= {'Related Coy'}>} days9) + Sum({<SEGMENT -= {'Related Coy'}>} above12) + Sum({<SEGMENT -= {'Related Coy'}>} curren)) > (Sum({<SEGMENT -= {'Related Coy'}>} if(L5M,sales))),

(Sum({<SEGMENT -= {'Related Coy'}>} days3) + Sum({<SEGMENT -= {'Related Coy'}>} days6) + Sum({<SEGMENT -= {'Related Coy'}>} days9) + Sum({<SEGMENT -= {'Related Coy'}>} above12) + Sum({<SEGMENT -= {'Related Coy'}>} curren)), 0)

[paulyeo11]

Hi Sunny

Many thank for your help.

it work fine.

Paul