Smoothing BackOrder - Set Analysis - Aggr

[EdgarMM]

Hi Gurus

            I hope you can help me with this requirement; I create two pivot tables, the first one is showing the backorder per product and sales channel, the second pivot table is showing the over sales per product and sales channel as well; The information I have in these two tables is

BACKORDER
Product	Sales Channel	Ammount
Product B	Channel 2	117
Product D	Channel 3	100
		217

OVER SALES
Product	Sales Channel	Ammount
Product A	Channel 4	7
Product B	Channel 2	97
Product D	Channel 3	83
Product C	Channel 1	5
		192

            The table I’m looking for should be something like this

EXPECTED TABLE
Product	Sales Channel	Ammount
Product B	Channel 2	20
Product D	Channel 3	17
		37

            The information will change per month and year based on user’s selection, knowing that, I cannot set sales channel as fixed in the third table:

            The script for the first table is:

                       Aggr(sum({$<Sales_Order_Year = {$(#=Only(Sales_Order_Year))}, [Budget]={'Yes'}, Source = {'ERP'}, Sales_Order_Item_Rejection_Reason = {'11', '15'}>}
                (IF($(vDocuments), (Domestic_Amount_MXP)))), Sales_Channel  )

            The Script for the second table is:

sum(aggr(
IF((
   (sum({$<Sales_Order_Year = {$(#=Only(Sales_Order_Year))}, [Budget]={'Yes'}, Source = {'ERP'}, Sales_Order_Item_Rejection_Reason = {' ' , '11', '15'}>}
   (IF($(vDocuments), Domestic_Amount_MXP))))
   /
   (sum({$<Sales_Order_Year = {$(#=Only(Sales_Order_Year))}, [Budget]={'Yes'}, Source={'$(=$(Versus))'}>} Gross_Amount_MXP))
   )>1,

   (
   (sum({$<Sales_Order_Year = {$(#=Only(Sales_Order_Year))}, [Budget]={'Yes'}, Source = {'ERP'}, Sales_Order_Item_Rejection_Reason = {' ' , '11', '15'}>}
   (IF($(vDocuments), Domestic_Amount_MXP))))
   -
   (sum({$<Sales_Order_Year = {$(#=Only(Sales_Order_Year))}, [Budget]={'Yes'}, Source={'$(=$(Versus))'}>} Gross_Amount_MXP))
   )
  
,0
), Product, Sales_Channel)
)

          The information from these two tables is being shown in a waterfall graph

            The first bar is showing 217 (following the example of the first table related to BackOrder), the second bar is showing 192 (related to the second table for Over Sales) and the third bar should be showing 37 since we are looking the difference between these two numbers only for those cases in which the products and the sales channel coincide.

            I will appreciate your help on this.

Regards,

Edgar.

[sunny_talwar]

Did you try using Expression 1 - Expression 2 for difference?

[EdgarMM]

Hi Sunny

     I tried using that expression but in my example the reusult was 25 (217 - 192 =  25), and what I'm looking for is using Expr 1 - Expr 2 but only for those products and sales channels that exists in both pivot tables, in my example should be product B and D in combination with Sales Channel 2 and 3, in that case the result should be 217 - 180 = 37.

Regards,

Edgar.

[Brett_Bleess]

The only idea that comes to mind here is to use some sort of flag field that could be used in the expressions to get the 'matched' values of which you spoke ignoring those that do not match, best idea that popped into my head, maybe someone else will be able to expand upon it for you.

Regards,
Brett