Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
Maybe like this?
if(sum({<CREDIT_CODE_FLAG={'Yes'},TRX_TYPE={'Invoice'},CUSTOMER_TYPE-={'ITC'}>} rangesum(AMOUNT,-COST,CB_AMT))<0
, sum({<CREDIT_CODE_FLAG={'Yes'},TRX_TYPE={'Invoice'},CUSTOMER_TYPE-={'ITC'}>} rangesum(AMOUNT,-COST,CB_AMT)))
May be like this:
=RangeMin(Sum({$<TRX_TYPE = {'Invoice'}, CREDIT_CODE = {'Yes'},CUSTOMER_TYPE = {*}-{XYZ}>} AMOUNT), 0)
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,
Would you be able to show few rows of data to see the issue her?
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))
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.
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
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
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:
Would you please share application to look intooo