Aggr function, and if statement, plus remove some values

[crystles]

In the chart I have a set of products that are generally sold by the Company. That Company is being compared to the entire market that the Company belongs to. If the Product price is LESS then the Average Market price, then you subtract them, to get the potential money that is lost on this company.

As you an see, the product, Seats, has no Potential, because the Average Market price is less then the company price.

I need to find a way to get this formula for the Potential by Market, for each Company in a separate chart, that only has the Companies listed. This means that it does not need to count the Difference of the Company Sales and the Average Market sales, if the Product has no Potential. But everything I have tried, still subtracts the two and gives me the Potential for everything from that Company, and not what I want, only the Products that provide Potential sales.

I believe I need an Aggr() function for the Products but I am not sure how to get them to compare the Company to the Avg of the Market for each Product and then only sum the Products that have Potential.

I have a table in my QV document that is similar to the one below and the Potential expression I have works fine there. The only problem is if I remove the Products, then formula doesn't act right. Here is my formula

(if(Num($(vAvgSalesForSize)-$(vAvgSalesForSelectedCompany) )<=0,0,Num($(vAvgSalesForSize)-$(vAvgSalesForSelectedCompany) )))

I have tried to break this down as easily as I could, but if I need to make anything more clear please let me know.

For Company ABC

In Size "Small" Market

Product	Company ABC Sales	Avg Small Market Sales	Potential
Tires	$50	$75	$25
Windshield	$100	$120	$20
Seats	$20	$10	0
Total Potential			$45

[crystles]

That is an amazing elegant solution to my original equation. It works perfectly, just like the original, but with much less code involved. Thank you for sharing this approach.

Unfortunately it only works if I have the Company selected. I am beginning to think that it is the underlying formulas that are causing the issue, since I wrote it initially, for the User to have to specifically select the Company they wanted to have the formula calculated for.

I have broken down all of variables, and I was wondering if anyone can see where the issue might be?

I wanted to make sure all the pieces were included in case the issue is somewhere inside the other pieces of the formula that I had not explained before. Thanks!

(NOTE, I actually have an extra step where I divide the Sum of Sales by the number of Products, so that is the extra step you are seeing for "Product_Count")

(NOTE 2: I also have to "Annualize" the sum, for the amount of days from the beginning of our fiscal year (96) so I divide the sum by that, then multiply by 365)

sum(Aggr( RangeMax(
//$(vAvgSalesForSize)
((Sum( {1<MarketSize=p(MarketSize),FY_Year={"$(=max([FY_Year]))"}>}ORDER_AMT)/96)*365)
/
(Sum({1<MarketSize=p(MarketSize)>} Product_Count))
-
//$(vAvgSalesPerCompany)
(((Sum({<FY_Year={"$(=max([FY_Year]))"}>}ORDER_AMT))/96)*365)/Sum(Product_Count)
,

0), Product))*Sum(Product_Count)

[rwunderlich]

It would be useful if you could upload a small sample. You can reduce and scramble the data.

Preparing examples for Upload - Reduction and Data Scrambling

Attached is a simple example, but it would be useful to have the additional fields.

-Rob

[crystles]

I will try to load an example later today. But I did find something interesting.

I broke the formula up, between the AvgSalesForCompany and AvgSalesForMarket. When the Company is selected, the two numbers are different, and when subtracted, give me the correct answer. But when the Company is not selected and it is showing all the Companies in the list, the two numbers are the exact same.

These numbers are actual rounded values from a selection of one of the Companies to give you a better idea.

EXAMPLE

Basic Formula: (AvgSalesForCompany)  -  (AvgSalesForMarket)  =   Potential

With Company Selected

AvgSalesForCompany = 1,300,000

AvgSalesForMarket = 800,000

Potential = 70,000

WithOUT Company Selected

AvgSalesForCompany = 43,000

AvgSalesForMarket = 43,000

Potential = 0

[crystles]

OK, I did figure out a round about way of creating this formula, though not in the original way I had wanted to.

I had to create a pivot table and add the Products as a dimension.

Then I created AvgForMarketSize field with this formula:

((Sum({<INVC_FY_Year={"$(=max([INVC_FY_Year]))"}>}
Aggr(
Sum({<INVC_FY_Year={"$(=max([INVC_FY_Year]))"}>}Total
<DealerMarketSize,CC_L1_Group> SHIP_ORDER_AMT), DealerMarketSize, CUSTOMER_NAME, CC_L1_Group))
/96)*365)
/Sum(Total {<DealerMarketSize={'Small'}>} VIO_Count)

That gave me the average I needed for the Market size and then I just placed that and the Sum for the individual customer into a formula like I had before and it gave me the correct output for the Potential.

The only problem now is, with the Pivot table, it only works when the Products are showing (broken out) in the pivot table. I still need to find a way for it to apply the formula only to the Potential values and sum them up.

Thank you for everyones help!