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,

[trdandamudi]

Hope this is what you are looking for... Please see the attached file...

[chinnuchinni]

Hi Adams......You can try with this,I hope you will get.....

=sum({<Margin={"<=0"}>}Sales)

[t_moutault]

Hi joe,

try this in the Dimension

[jadams123]

I am new to QV how would i be able to see .qvw file?

[trdandamudi]

If you are in the Inbox then you will not be able to see the attachment. So don't sign on with your Id. Just go to the link land you will be able to see the attachment. Hope this helps...

[jadams123]

hey i am almost there...i just need one more answer...i think it was the variable that wasnt working out. I am not sure why?

vmargin: Amount - Cost + Chargback

MARGIN

if(sum(AMOUNT - COST + CB_AMT)<0,sum(AMOUNT - COST + CB_AMT))

basically all your answer were right: MARGIN: if(sum(margin(<0,sum(margin))

and sales assocaited with it: SALES: if(sum(margin)<0,sum(amount))

I want to know how can i exclude/include the below dimensions in my formula

1) CREDIT_CODE_FLAG = {'Yes'}

2) TRX_TYPE = {'Invoice'}

3) CUSTOMER_TYPE = {*} - {ITC}

I know i am doing something stupid here:

if(sum({$<CREDIT_CODE_FLAG = {'Yes'}, TRX_TYPE = {'Invoice'}, CUSTOMER_TYPE = {*} - {ITC}>}(AMOUNT - COST + CB_AMT)<0,sum({$<CREDIT_CODE_FLAG = {'Yes'}, TRX_TYPE = {'Invoice'}, CUSTOMER_TYPE = {*} - {ITC}>}(AMOUNT - COST + CB_AMT))

Thanks

[jadams123]

anyone can help me here?

[johnw · Accepted solution]

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)))