topic Re: How to add sales of two products in QlikView

How to add sales of two products

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

Hi,

I have a scenario where i have to add total sales(Expected Sales) of two products(Product A and Product B).

Please find the snapshot below:

I want to get the result as $69,750,000.

Kindly help.

Please find the Qvw file attached. !

Re: How to add sales of two products

sunny_talwar — Wed, 16 Mar 2016 15:40:13 GMT

May be this:

=Sum(TOTAL Aggr((if(Products='Product A' or Products='Product B',[Confirmed Orders(Qty)])

*(if(Products='Product A',30000,40000))

*(1+if(Products='Product A',Price_Esc_A_%,Price_Esc_B_%))

*(if(Products='Product A',Profitability_CO_A,Profitabilty_CO_B)))

+(if(Products='Product A' or Products='Product B',[High Risk(Qty)])

*(if(Products='Product A',30000,40000))

*(1+if(Products='Product A',Price_Esc_A_%,Price_Esc_B_%))

*if(Products='Product A',$(High_Risk_A),$(High_Risk_B))), Products))

Re: How to add sales of two products

farheenayesha — Wed, 16 Mar 2016 16:59:40 GMT

Thank you so much Sunny

Re: How to add sales of two products

sunny_talwar — Wed, 16 Mar 2016 17:00:39 GMT

No problem at all

Re: How to add sales of two products

farheenayesha — Wed, 16 Mar 2016 17:13:58 GMT

Hi Sunny,

I am facing the same issue with the Expected total cost in the second table. could you please help ,me to add those values. Please find the snapshot below:

I want to add the Expected Total cost of both the products. Kindly help.

Re: How to add sales of two products

sunny_talwar — Wed, 16 Mar 2016 17:18:42 GMT

You won't be able to use the column labels, but this gets you what you are looking for:

=Sum(TOTAL Aggr((if(Products='Product A' or Products='Product B',if(Products='Product A',[Material Cost / Unit]*(1-Expected_Productivity_1),[Material Cost / Unit]*(1-Expected_Productivity_1_B)))*(sum(if(Products='Product A' or Products='Product B',[Confirmed Orders(Qty)])) + sum(if(Products='Product A' or Products='Product B',[High Risk(Qty)]))))

+(if(Products='Product A' or Products='Product B',if(Products='Product A',[Labor Cost/Unit]*(1-Expected_Productivity_2),[Labor Cost/Unit]*(1-Expected_Productivity_2_B)))*(sum(if(Products='Product A' or Products='Product B',[Confirmed Orders(Qty)])) + sum(if(Products='Product A' or Products='Product B',[High Risk(Qty)]))))

+(if(Products='Product A' or Products='Product B',if(Products='Product A',[Transportation Cost/Unit]*(1-Expected_Productivity_3),[Transportation Cost/Unit]*(1-Expected_Productivity_3_B)))*(sum(if(Products='Product A' or Products='Product B',[Confirmed Orders(Qty)])) + sum(if(Products='Product A' or Products='Product B',[High Risk(Qty)])))), Products))

Re: How to add sales of two products

farheenayesha — Wed, 16 Mar 2016 23:54:55 GMT

Thank you so much Sunny Could you please tell how to use those expressions in text box? i have to show the total expected sales in text box but when i use those exp in text box i am getting blank.

Thanks once again for your help

Re: How to add sales of two products

sunny_talwar — Thu, 17 Mar 2016 01:29:38 GMT

Seems to be working for me:

=Money(Sum(Aggr((if(Products='Product A' or Products='Product B',[Confirmed Orders(Qty)])

*(if(Products='Product A',30000,40000))

*(1+if(Products='Product A',Price_Esc_A_%,Price_Esc_B_%))

*(if(Products='Product A',Profitability_CO_A,Profitabilty_CO_B)))

+(if(Products='Product A' or Products='Product B',[High Risk(Qty)])

*(if(Products='Product A',30000,40000))

*(1+if(Products='Product A',Price_Esc_A_%,Price_Esc_B_%))

*if(Products='Product A',$(High_Risk_A),$(High_Risk_B))), Products)))

=Money(Sum(Aggr((if(Products='Product A' or Products='Product B',if(Products='Product A',[Material Cost / Unit]*(1-Expected_Productivity_1),[Material Cost / Unit]*(1-Expected_Productivity_1_B)))*(sum(if(Products='Product A' or Products='Product B',[Confirmed Orders(Qty)])) + sum(if(Products='Product A' or Products='Product B',[High Risk(Qty)]))))

Re: How to add sales of two products

farheenayesha — Thu, 17 Mar 2016 06:05:19 GMT

Hey Sunny you are genius. Appreciate your knowledge and your great help Thank you so much!!