Sales associated with negative margin

[jadams123]

Hi Guys:

So i have a problem here...

I have this column that has negative margin.

I know how to get negative margin.

=sum($(vMargin)<0)

but how do you get sales associated with that negative margin?

I know is is the wrong formula...but i am trying to get this:

=if(sum($(vMargin)<0),Sum( {$<TRX_TYPE = {'Invoice'}, CREDIT_CODE = {'Yes'},CUSTOMER_TYPE = {*}-{XYZ}>} AMOUNT ))

so basically this means:

1)if the margin is negative

than

2) sum sales (including "invoice",including credit code "Yes", and excluding "XYZ"

Thanks,

[sunny_talwar]

May be like this:

=RangeMin(Sum({$<TRX_TYPE = {'Invoice'}, CREDIT_CODE = {'Yes'},CUSTOMER_TYPE = {*}-{XYZ}>} AMOUNT), 0)

[jadams123]

No it didnt work..

Do remember that i have two columns: 1) sales 2) margin

i can make an expression in QV for "Margin" if its negative.

But how can i capture all the "Sales" related to those "negative margin".

Thanks,

[sunny_talwar]

Would you be able to show few rows of data to see the issue her?

[johnw]

Does * work, or must it be "*"? Does XYZ work, or must it be 'XYZ'? I can't remember. This may do nothing to change anything, but here's how I'd write it.

if(sum($(vMargin)<0),sum({<TRX_TYPE={'Invoice'},CREDIT_CODE={'Yes'},CUSTOMER_TYPE-={'XYZ'}>} AMOUNT))

[jadams123]

This is what i want.

They way i find negative margin is: sum($(vMargin)<0)

How to i find all the sales with negative margin.

John - I'll try your suggestion.

[Anil_Babu_Samineni]

can you please share the Data along with Expected O/p

Yes, You've shared the Data. My Data is you have and you are expecting My Pivot / What? I am not sure i udnerstand your concern

[jadams123]

So let me try to explain this:

1) I have a set of data which has "Sales", "Margin", etc

2) Margin has some negatives and some positives

3) First, I Want to filter all negative numbers in "Margin". sum($(vMargin)<0)

4) Then i want to filter all sales related to that negative margin.

When I try to do this...my formula filters out all negative margin in "Margin" column.

But in Sales column it also sums up all sales related to positive margin ($11,328). I dont want this number to show up. 

I want to be able to say in my "Sales" expression:

If Margin is negative

than

Sum sales related to negative margin. Do not include sales related to positive margin.

Thanks,

J

[jadams123]

By the way this is all i want:

I have data with "margin". Some are positive and some are negative margin

I want to filter negative margin and show Sales, Quantity e.t.c associated with that negative margin.

How do i do this:

[Anil_Babu_Samineni]

Would you please share application to look intooo