Selling price index and aggr()

[#1]

Hello,

I have a table which looks like the following

SalesOffice	Product	Date	Year	Quantity	Value
1000	ABC	12/10/2015	2015	5	100
1000	ABC	12/09/2014	2014	10	75
1200	DEF	12/08/2015	2015	10	200
1000	DEF	12/08/2015	2015	20	150
1000	GHI	12/08/2014	2014	7	90
1200	DEF	12/07/2014	2014	5	45

I was asked to calculate a selling price index, which can be summed up like this:

([2015 sales])/([2015 quantities]*(If ([2014 average price] = 0, [2015 average price],[2014 average price]))*100;

I use this function, which works fine per product:

=Sum(Aggr(Sum({$<Year={$(=max(Year))}>}[Quantity])*Sum({$<Year={$(=max(Year))}>}[Value])/Sum({$<Year={$(=max(Year))}>[Quantity]),Product))

/

Sum(Aggr(Sum({$<Year={$(=max(Year))}>}[Quantity])*

(If(IsNull(Sum({$<Year={$(=max(Year)-1)}>}[Value])/Sum({$<Year={$(=max(Year)-1)}>}[Quantity]))

  or Sum({$<Year={$(=max(Year)-1)}>}[Value])/Sum({$<Year={$(=max(Year)-1)}>}[Quantity]) = 0,

  Sum ({$<Year={$(=max(Year))}>}[Value])/Sum({$<Year={$(=max(Year))}>}[Quantity]),

  Sum ({$<Year={$(=max(Year)-1)}[Value])/Sum({$<Year={$(=max(Year)-1)}>}[Quantity]))),Product))

*100

But in some cases, on sales office level, I have sales for 2015 and 2014, but this function returns a null. However, as soon as I select only one sales office, then the result is correct.

I suppose I don't use the aggr() function correctly, so I'd like some advice, of course.

[jonathandienst]

The dimensions in the Aggr() must include all the dimensions in the table -- in this case it looks like SalesOffice, Product, Date, Year. So the Aggr would look like:

     Sum(Aggr(Sum(.....), SalesOffice, Product, Date, Year))

[Colin-Albert]

As Jonathan says, the Aggr statement must include all dimensions.

Also when using Aggr with set expressions, the set expression must appear twice, in both the Aggr expression and the containing sum expression.

Henric hic recently posted an excellent blog on using Aggr  Pitfalls of the Aggr function

[#4]

Thanks, I found the first post about Aggr() that Henric posted, but did not see the one about pitfalls. That led to a serie of interesting reads!

I'll try to fix my function before I flag this solution as answered.

[#5]

After a night of sleep, I'm less confused by the aggr() function (I think), but I don't think that my function can do what I need to do on sales office level...

I corrected my basis table for my example.

SalesOffice	Invoice	Product	Date	Year	Quantity	Value
1000	123	ABC	12/10/2015	2015	5	100
1000	456	ABC	12/09/2014	2014	10	75
1200	789	DEF	12/08/2015	2015	10	200
1000	234	DEF	12/08/2015	2015	20	150
1000	567	GHI	12/08/2015	2015	8	90
1200	890	DEF	12/07/2014	2014	5	45

In Excel, I would use sum.if to get this table first to calculate the selling price index by product.

The most important columns would be the last three:

• Qty2015 with Avg2015 = Value2015 actually
• Qty2015 with Avg2014 = Qty2015 * (If(Avg2014 > 0, Avg2014, Avg2015)
• SPI = [Qty2015 with Avg2015]/[Qty2015 with Avg2014]*100

SalesOffice	Product	Qty2014	Qty2015	Value2014	Value2015	Avg2014	Avg2015	Qty2015 with Avg2015	Qty2015 with Avg2014	SPI
1000	ABC	10	5	75	100	7,5	20	100	37,5	266,67
1000	DEF	0	20	0	150	0	7,5	150	150	100,00
1000	GHI	0	8	0	90	0	11,25	90	90	100,00
1200	DEF	5	10	45	200	9	20	200	90	222,22

Then, I'd sum these column to have the SPI by SalesOffice

SalesOffice	Qty2015 with Avg2015	Qty2015 with Avg2014	SPI
1000	340	277,5	122,52
1200	200	90	222,22

I'm not sure the function I used is right for this. Is it?