Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

crystles
Contributor III

Aggr function, and if statement, plus remove some values

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

ProductCompany ABC SalesAvg Small Market SalesPotential
Tires$50$75$25
Windshield$100$120$20
Seats$20$100
Total Potential$45
13 Replies

Re: Aggr function, and if statement, plus remove some values

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

p_verkooijen
Valued Contributor

Re: Aggr function, and if statement, plus remove some values

SUM(AGGR(

  IF(AVG({<Company>}Sales)-SUM(Sales)>0

  , AVG({<Company>}Sales)-SUM(Sales)

  , 0)

  , Product

  ))


Also see attachment

crystles
Contributor III

Re: Aggr function, and if statement, plus remove some values

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

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

crystles
Contributor III

Re: Aggr function, and if statement, plus remove some values

I added Company and it immediately made the value $0, even with the Company selected. I thought for sure that would solve the issue.

crystles
Contributor III

Re: Aggr function, and if statement, plus remove some values

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

Are you sure there are no syntax errors? Can you share a sample if possible?

p_verkooijen
Valued Contributor

Re: Aggr function, and if statement, plus remove some values

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

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

http://masterssummit.com

http://qlikviewcookbook.com

Community Browser