10 Replies Latest reply: May 14, 2016 2:14 PM by Tom Dabek

# How to recalculate expression based on individual values in a table that displays sum

I have a problem where I want to show the "Price Index" in a row of a table for a dimension that shows sums for other expressions.

I have a data source like this:

I would like to create a report basically like this - but I cannot figure out how to complete the last column, 'Price Index'

In excel I can demonstrate how the Price Index is calculated.  It is basically a weighted average of the price increases, with the weighting being the 2016 revenue.  The entire set of data gives a price index of 11.6%, the Price Index for each salesperson is shown above in the sample report: 5.2% and 12.5%

However the rows in the report are sum functions, with the sum of quantity and sum of revenue (both hidden) and divided by each other to get a price for both 2015 and 2016.  The price index requires discrete values to be compared to each other and then added together etc to finally get the result.  How can something like this be put into a report column?

Below is a how the price index is calculated:

Is this possible?

Thank you

• ###### Re: How to recalculate expression based on individual values in a table that displays sum

Something like this?

I created a straight table with 4 expressions

1) =SUM({<YEAR = {\$(=Max(YEAR) - 1)}>}REVENUE)/Sum({<YEAR = {\$(=Max(YEAR) - 1)}>}QTY)

2) =SUM({<YEAR = {\$(=Max(YEAR))}>}REVENUE)/Sum({<YEAR = {\$(=Max(YEAR))}>}QTY)

3) =Column(2)/Column(1) - 1

4) =Sum(Aggr((((SUM({<YEAR = {\$(=Max(YEAR))}>}REVENUE)/Sum({<YEAR = {\$(=Max(YEAR))}>}QTY))/(Sum({<YEAR = {\$(=Max(YEAR) - 1)}>}REVENUE)/Sum({<YEAR = {\$(=Max(YEAR) - 1)}>}QTY))) -1) * SUM({<YEAR = {\$(=Max(YEAR))}>}REVENUE), PRODUCT, SALESPERSON))/SUM({<YEAR = {\$(=Max(YEAR))}>}REVENUE)

This right now will work with comparison of 2 years. Is this always going to be a 2 year comparison or could there be a comparison of more than 2 years?

I also tried a version where you can do this in Pivot Table, but was only able to get Price Change (Got too lazy to fix the Price Index). If you like the Pivot Table version, I can get that working too

Dimension

1) SALESPERSON

2) =Pick(DIM, YEAR & ' Price', 'Price Change', 'Price Index')

Expression

Pick(DIM,

Num(Sum(REVENUE)/Sum(QTY), '#,##0.00'),

Num((Sum(TOTAL <SALESPERSON> {<YEAR = {\$(=Max(YEAR))}>}REVENUE)/Sum(TOTAL <SALESPERSON> {<YEAR = {\$(=Max(YEAR))}>}QTY))/(Sum(TOTAL <SALESPERSON> {<YEAR = {\$(=Max(YEAR)-1)}>}REVENUE)/Sum(TOTAL <SALESPERSON> {<YEAR = {\$(=Max(YEAR)-1)}>}QTY)) - 1, '##.0%'))

• ###### Re: How to recalculate expression based on individual values in a table that displays sum

Wow, this is brilliant!

Thank you.

The aggr function seems very powerful.

Yes the comparison will always be over two years, however I can see where it would be interesting to compare current values to a point in time n years ago.  I am assuming in a case like this one would just change (YEAR)-1 to (YEAR)-n in the set analysis portion of your statement?

I am going to now try and recreate this in my application with large sets of data - it seems to me at first glance it should work based on your use of SALESPERSON, PRODUCT in the set analysis part.

I do not typically use pivot tables, but that may be an interesting solution as well since I often have users who prefer this format in excel files.

• ###### Re: How to recalculate expression based on individual values in a table that displays sum

I have a followup question - i am testing with my real data and have come across a situation where the solution needs to be modified slightly:

How to change the expression below so that instances where the quantity or revenue in either 2016 or 2015 are negative are excluded from the price index calculation.

I have been trying to put a statement like

if(sum({\$<YEAR={\$(=max(YEAR))}, PRODUCT, SALESPERSON=>}QTY)>0, <do the calculation>)

but with no luck, i can't seem to figure out how to incorporate this or where to put it.

If i can get that working, i can then try and make it into an IF statement using AND to combine the 4 scenarios - check for negative QTY in 2016, negative QTY in 2015, negative REVENUE in 2016 and negative REVENUE in 2015

=Sum(Aggr((((SUM({<YEAR = {\$(=Max(YEAR))}>}REVENUE)/Sum({<YEAR = {\$(=Max(YEAR))}>}QTY))/(Sum({<YEAR = {\$(=Max(YEAR) - 1)}>}REVENUE)/Sum({<YEAR = {\$(=Max(YEAR) - 1)}>}QTY))) -1) * SUM({<YEAR = {\$(=Max(YEAR))}>}REVENUE), PRODUCT, SALESPERSON))/SUM({<YEAR = {\$(=Max(YEAR))}>}REVENUE)

• ###### Re: How to recalculate expression based on individual values in a table that displays sum

Price index calculation only? You want it to be included in the other three expressions?

• ###### Re: How to recalculate expression based on individual values in a table that displays sum

Yes, just the price index.

These negatives usually have little impact in the overall average price and are often legitimate - for example a credit issued to a customer.

However in the calculation for price index they can have a massive impact that is not truly reflecting reality, so it makes sense to remove these from the calculation

• ###### Re: How to recalculate expression based on individual values in a table that displays sum

May be this?

=Sum(Aggr((((SUM({<YEAR = {\$(=Max(YEAR))}>}If(PRICE > 0 and QTY > 0, REVENUE))/Sum({<YEAR = {\$(=Max(YEAR))}>}If(PRICE > 0 and QTY > 0, QTY)))/(Sum({<YEAR = {\$(=Max(YEAR) - 1)}>}If(PRICE > 0 and QTY > 0, REVENUE))/Sum({<YEAR = {\$(=Max(YEAR) - 1)}>}If(PRICE > 0 and QTY > 0, QTY)))) -1) * SUM({<YEAR = {\$(=Max(YEAR))}>}If(PRICE > 0 and QTY > 0, REVENUE)), PRODUCT, SALESPERSON))/SUM({<YEAR = {\$(=Max(YEAR))}>}If(PRICE > 0 and QTY > 0, REVENUE))

• ###### Re: How to recalculate expression based on individual values in a table that displays sum

Hi Sunny

Let me try it and post back, i will be offline for a couple days so unfortunately have to delay my response.

Thanks again for your help on this.

• ###### Re: How to recalculate expression based on individual values in a table that displays sum

Hi, I will try this again but with my actual data because I can't go from the sample to the real with it still working...

Here is my rationale with my current understanding of these functions.

(let me know if i should start a new thread for this)

I have a straight table that groups by dimension Customer_Major_code

weight is an expression:

=sum(aggr(if(sum({\$<year={\$(=max(year-1))},Month=>}Revenue_Total)>0,

if(sum({\$<year={\$(=max(year))},Month=>}Revenue_Total)>0,

if(sum({\$<year={\$(=max(year))},Month=>}Volume)>0,

if(sum({\$<year={\$(=max(year-1))},Month=>}Volume)>0,

sum(Revenue_Total))))),Customer_Major_code,PlantItemEndUse))

so for each Customer_Major_code, weight is the Revenue_Total for each additional dimension PlantItemEndUse within the Customer_Major_code based on the aggr function that seeks to only consider PlantItemEndUse when it has a positive value for Volume and Revenue_Total for current and previous year.

This works

I need another expression that will be the sum of the individual dimensions in the aggr expression - PlantItemEndUse  using the same rules/criteria.

so for example, if a particular Customer_Major_code, lets call it "DIY" has within it 10 different PlantItemEndUse values, i want to go through each of those 10 rows of PlantItemEndUse rows, and if both current year (Revenue_Total/Volume)>0 and previous year (Revenue_Total/Volume)>0 then calculate the percentage change for that PlantItemEndUse and multiply it by the Revenue_Total for the current year.  When the 10 instances of these are added up i get a total that is finally divided by the expression weight shown above.