Nested Aggr with If statement not working

[crystles]

I have a dashboard where the users can select a customer and a table that shows the customers Products and their average sales per product and how they compare to the average sales of the market that the customer belongs to. This table works perfectly. The only problem is I need to create a different table that basically shows the results of this table, for every customer, all in one table, instead of showing the selected customer, one at a time.

The table that is currently working  looks like this. If the Market sales for a product is greater than the average customer sales, then there is lost "Potential" for sales for that customer, so we count that amount and multiply it by the total products sold by that customer. IF the customer sales average is greater than the market average, than they are doing better than the market and there is no Potential for more sales, so we don't count that in the totals.

Product	Avg Customer Sales	Avg Market Sales	Potential
Engine	$10	$20	$10*1000=$10,000
Tires	$50	$30	$0*1000=$0
Doors	$40	$60	$20*1000=$20,000
Totals	$100	$110	$30,000

* Number of units sold by dealer = 1000

The formula for the Avg of the Customer sales for this table is this:

Round( ((if((
IsNum( Sum(Aggr(Sum({<INVC_FYADJUST={0}>} SHIP_ORDER_AMT),CUSTOMER_ID,CC_L1_Group)))),
(Sum(Aggr(Sum({<INVC_FYADJUST={0}>} SHIP_ORDER_AMT),CUSTOMER_ID,CC_L1_Group))),0)
*
($(vProduct_Count))
,0.01)

I have to round the values in order to get the correct totals.

INVC_FYADJUST = collects only the current year sales

SHIP_ORDER_AMT = the collection of sales amounts

CUSTOMER_ID = the customer selected

CC_L1_Group = the product

The Avg for Market sales is this:

Round(( (
Sum({1} DISTINCT
Aggr(Sum({<INVC_FYADJUST={0},MarketSize=P(MarketSize)  ,CUSTOMER_PARENT_ID=,CUSTOMER_PARENT_BP=, CUSTOMER_ID=,CUSTOMER_NAME=>}DISTINCT TOTAL <CC_L1_Group> MarketSum),CUSTOMER_PARENT_ID,CC_L1_Group))
*
(Sum(DISTINCT TOTAL <MarketSize>  MarketCount))
,0.01) 

These formulas work in the table and the potential formula below works too.

(Sum(DISTINCT Aggr(
if(
//Market Avg
$(vPTNL_MarketAvgSales)

-
//Customer Avg
$(vPTNL_CustomerAvgSales)
<=0,0,
//Market Avg
$(vPTNL_MarketAvgSales)
-
//Customer Avg
$(vPTNL_CustomerAvgSales)
),CC_L1_Group)
))
* $(vProduct_Count)

The problem is, when I try to remove the production dimension and have the Customer as a dimension, none of the formula works for the potential. I need to aggr it differently I am assuming, but I have tried numerous different combinations and nothing has really worked. I did finally get one formula to work somewhat, but it is not giving the correct answers. Here is the formula for the table I need so far. it is not giving me the correct results for each Customer though... not sure why.

Sum(DISTINCT TOTAL <CUSTOMER_PARENT_BP, CC_L1_Group>
Aggr(
if(
//Market Avg
$(vPTNL_MarketAvgSales)

-
//Customer Avg
$(vPTNL_CustomerAvgSales)
<=0,0,
//Market Avg
$(vPTNL_MarketAvgSales)
-
//Customer Avg
$(vPTNL_CustomerAvgSales)

), CUSTOMER_PARENT_BP,CC_L1_Group)) 
* $(vProduct_Count)

[swuehl]

I guess the reason why it's not working is that you are using this set expression part in your Avg for market:

MarketSize=P(MarketSize)

and the p() function will get the possible values depending on your customer selection. If you remove the selection then and use customer as dimension, the MarketSize set modifier will not react to the current dimension line, if that's what you are expecting (set analysis is only evaluated once per chart).

Regards,

Stefan

[crystles]

Yes I just now realized I did not add the correct Market formula that I used in the new table. I did remove the p() function. Here is the Market formula I am using in the new table I am trying to create.

((Sum(Aggr(
Sum({<INVC_FYADJUST={0}>}DISTINCT TOTAL <CUSTOMER_ID, CC_L1_Group> SHIP_ORDER_AMT),CUSTOMER_ID,CC_L1_Group))

* $(vProduct_Count)

[swuehl]

A distinct sum of an order amount looks a bit questionable to me.

Could you maybe post a small sample QVW and your requested results?

[crystles]

I have been trying to edit the formula down to post because it has a lot of pieces that I know work, but the aggregation down to the product level is what I cant get to. I was able to simplify the formula down, here is the actual formula I have right now. it gives me the difference between the avg market sum TOTAL and the customer/dealer avg sales TOTAL but thats not what I need.

I need it to go one layer beneath that and have it compare the price for the product between the dealer and the market and IF the market product is greater, then subtract them, if the dealer is greater, then just give me 0. But instead its just adding up all the products and subtracting the totals.

Aggr(
if(

//*********************** MARKET *********************
((Sum( DISTINCT TOTAL <DealerParentMarketSize>
ParentDealerSum)
/$(vDateAnnualizedVIO))*$(vDate_NumOfDaysCFY))/
(Sum({1}DISTINCT VIO_MarketParentCount)) 
-
//*********************** DEALER *********************
((Sum ( DISTINCT TOTAL <CUSTOMER_PARENT_BP> ParentDealerSum)
/$(vDateAnnualizedVIO))*$(vDate_NumOfDaysCFY))/
Sum(DISTINCT TOTAL <CUSTOMER_PARENT_BP> VIO_DealerCount)
//****************************************************
<=0
,0,
//*********************** MARKET *********************
((Sum( DISTINCT TOTAL <DealerParentMarketSize>
MarketSum)
/$(vDateAnnualizedVIO))*$(vDate_NumOfDaysCFY))/
(Sum({1}DISTINCT VIO_MarketParentCount)) 
-
//*********************** DEALER *********************
((Sum ( DISTINCT TOTAL <CUSTOMER_PARENT_BP> ParentDealerSum)
/$(vDateAnnualizedVIO))*$(vDate_NumOfDaysCFY))/
Sum(DISTINCT TOTAL <CUSTOMER_PARENT_BP> VIO_DealerCount)
//**************************************************** 
)
,DealerParentMarketSize,CUSTOMER_PARENT_BP,CC_L1_Group)

[crystles · Accepted solution]

I was finally able to figure out how to make the formula work, so I thought I would come back and explain what I did, in case someone else had this issue.

I was unable to get the formula to work in the Application level because I was trying to do too much in the formula (Layers) so I went back to the data model and created fields for the variables I was using and the formula finally worked.

Below is the formula I eventually ended up using, and it is much simpler than what I was trying before. I made variables of the Potential, so all I had to do was compare the Market Potential field to the Dealer Potential Field, instead of trying to compare the formulas.

(Sum(aggr(
if(
Sum(DISTINCT MarketPotential)-Sum(DISTINCT   DealerPotential)
<=0,0,
Sum(DISTINCT MarketPotential)-Sum(DISTINCT  DealerPotential)
)
,DealerParentMarketSize,CUSTOMER_PARENT_BP,CC_L1_Group))
)
*
(Sum(DISTINCT TOTAL <CUSTOMER_PARENT_BP> VIO_DealerCount))