
Re: Aggr function, and if statement, plus remove some values
Sunny Talwar Jan 26, 2016 4:01 PM (in response to Crystle Stamper)Have not tested it, but may be something along these lines:
=Sum(Aggr(If(Avg(Sales) > Sum({<Company = {'A'}>} Sales), (Avg(Sales)  Sum({<Company = {'A'}>} Sales)), Product))

Re: Aggr function, and if statement, plus remove some values
Crystle Stamper Jan 26, 2016 4:17 PM (in response to Sunny Talwar )Thank you for your formula. I tried inputting my formula that I have in the table already created, with the "Sum(Aggr())" function around it and it does work when I have that company selected. Unfortunatly, when I unselect the Company, it gives me a value of $0, for all the Companies Potential?
That is how I made this table to work. So the users can select the company and see their potential. But now I need a summary of all the companies potential.
So like if your formula, I cannot only have one Company "A" selected, I need that in the dimension, per row.

Re: Aggr function, and if statement, plus remove some values
Sunny Talwar Jan 26, 2016 4:13 PM (in response to Crystle Stamper)What if you add Company to the Aggr function?
=Sum(Aggr(If(Avg(Sales) > Sum({<Company = {'A'}>} Sales), (Avg(Sales)  Sum({<Company = {'A'}>} Sales)), Product, Company))

Re: Aggr function, and if statement, plus remove some values
Crystle Stamper Jan 26, 2016 4:15 PM (in response to Sunny Talwar )I added Company and it immediately made the value $0, even with the Company selected. I thought for sure that would solve the issue.

Re: Aggr function, and if statement, plus remove some values
Sunny Talwar Jan 26, 2016 4:19 PM (in response to Crystle Stamper)Are you sure there are no syntax errors? Can you share a sample if possible?





Re: Aggr function, and if statement, plus remove some values
Paul Verkooijen Jan 26, 2016 4:09 PM (in response to Crystle Stamper)SUM(AGGR(
IF(AVG({<Company>}Sales)SUM(Sales)>0
, AVG({<Company>}Sales)SUM(Sales)
, 0)
, Product
))
Also see attachment

AggrExample.qvw 151.0 K

Re: Aggr function, and if statement, plus remove some values
Crystle Stamper Jan 26, 2016 4:17 PM (in response to Paul Verkooijen )I tried this, but it only gave me a value of $0 for all Companies.


Re: Aggr function, and if statement, plus remove some values
Paul Verkooijen Jan 26, 2016 4:28 PM (in response to Crystle Stamper)You have to change your formula, because of the extra dimension you have to include the TOTAL at the AVG Sales expression.
SUM(AGGR(
IF(AVG(TOTAL <Product> Sales)SUM(Sales)>0
, AVG(TOTAL <Product> Sales)SUM(Sales)
, 0)
, Product, Company
))
Or use the Rangemax instead like rwunderlich suggested
SUM(AGGR(
RangeMax(AVG(TOTAL <Product> Sales)SUM(Sales), 0)
, Product, Company
))

Re: Aggr function, and if statement, plus remove some values
Rob Wunderlich Jan 26, 2016 4:22 PM (in response to Crystle Stamper)First, let me suggest that you can simplify by using RangeMax instead of if() to eliminate the negatives.
RangeMax(Potential, 0)
or
RangeMax(AvgCompanySales  ThisCompanySales, 0)
Since you want to do the RangeMax over Product, add the Aggr:
sum(Aggr( RangeMax(AvgCompanySales  ThisCompanySales, 0), Product))
Rob

Re: Aggr function, and if statement, plus remove some values
Crystle Stamper Jan 26, 2016 4:59 PM (in response to Rob Wunderlich )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)
Re: Aggr function, and if statement, plus remove some values
Rob Wunderlich Jan 26, 2016 5:19 PM (in response to Crystle Stamper)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

ComparePotential_960285.qvw 155.0 K

Re: Aggr function, and if statement, plus remove some values
Crystle Stamper Jan 27, 2016 9:37 AM (in response to Rob Wunderlich )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




Re: Aggr function, and if statement, plus remove some values
Crystle Stamper Jan 28, 2016 12:23 PM (in response to Crystle Stamper)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!