topic Re: Sales associated with negative margin in QlikView

Sales associated with negative margin

jadams123 — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Sales associated with negative margin

sunny_talwar — Thu, 27 Oct 2016 16:17:04 GMT

May be like this:

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

Re: Sales associated with negative margin

jadams123 — Thu, 27 Oct 2016 20:42:34 GMT

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,

Re: Sales associated with negative margin

sunny_talwar — Thu, 27 Oct 2016 21:09:08 GMT

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

Re: Sales associated with negative margin

johnw — Thu, 27 Oct 2016 21:22:53 GMT

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

Re: Sales associated with negative margin

jadams123 — Thu, 27 Oct 2016 23:02:21 GMT

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.

Re: Sales associated with negative margin

Anil_Babu_Samineni — Fri, 28 Oct 2016 01:32:08 GMT

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

Re: Sales associated with negative margin

jadams123 — Fri, 28 Oct 2016 02:28:44 GMT

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,

Re: Sales associated with negative margin

jadams123 — Fri, 28 Oct 2016 02:41:14 GMT

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:

Re: Sales associated with negative margin

Anil_Babu_Samineni — Fri, 28 Oct 2016 04:18:04 GMT

Would you please share application to look intooo

Re: Sales associated with negative margin

trdandamudi — Fri, 28 Oct 2016 04:56:37 GMT

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

Re: Sales associated with negative margin

chinnuchinni — Fri, 28 Oct 2016 07:00:18 GMT

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

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

Re: Sales associated with negative margin

t_moutault — Fri, 28 Oct 2016 07:10:44 GMT

Hi joe,

try this in the Dimension

Re: Sales associated with negative margin

jadams123 — Fri, 28 Oct 2016 10:15:18 GMT

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

Re: Sales associated with negative margin

trdandamudi — Sun, 30 Oct 2016 11:47:04 GMT

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

Re: Sales associated with negative margin

jadams123 — Sun, 30 Oct 2016 14:45:37 GMT

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

Re: Sales associated with negative margin

jadams123 — Tue, 01 Nov 2016 09:58:58 GMT

anyone can help me here?

Re: Sales associated with negative margin

johnw — Tue, 01 Nov 2016 15:51:20 GMT

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