Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
farheenayesha
Creator
Creator

How to add sales of two products

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:

Capture.JPG

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

Kindly help.

Please find the Qvw file attached. !

1 Solution

Accepted Solutions
sunny_talwar

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


Capture.PNG



View solution in original post

8 Replies
sunny_talwar

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


Capture.PNG



farheenayesha
Creator
Creator
Author

Thank you so much Sunny

sunny_talwar

No problem at all

farheenayesha
Creator
Creator
Author

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:

Capture1.JPG

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

sunny_talwar

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

farheenayesha
Creator
Creator
Author

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

sunny_talwar

Seems to be working for me:

1)

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

2)

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

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

Capture.PNG

farheenayesha
Creator
Creator
Author

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