13 Replies Latest reply: Mar 18, 2016 7:21 AM by Gysbert Wassenaar

# Comparison sum sales in Year with Previous Year

Hi!

I need to create straight table where Dimensions are Customer, Product, Storage, Year

and Expressions are sum(Sales) in Year and sum(Sales) in Previous Year.

Something like this:

where number in column F is Sales in 2015 and number in column G is Sales in 2015 -1

Any idea how can I do it?

Many thanks!

• ###### Re: Comparison sum sales in Year with Previous Year

There are two ways to do this:

1) Either use the As-of table in the script (The As-Of Table)

2) or use Above(TOTAL ...) method.

For a more precise help, can you provide a sample?

• ###### Re: Comparison sum sales in Year with Previous Year

I attached small example

• ###### Re: Comparison sum sales in Year with Previous Year

Gysbert's solution seems to be working, isn't it? or are you looking for something else?

• ###### Re: Comparison sum sales in Year with Previous Year

It is correct when Year doesn't select

and if select Year, for example 2016, it's not correct

• ###### Re: Comparison sum sales in Year with Previous Year

See attached example

• ###### Re: Comparison sum sales in Year with Previous Year

thanks, it very expensive to change script, cause we'll need to add set analysis with Period=Current at all objects in many reports.

• ###### Re: Comparison sum sales in Year with Previous Year

Only in the reports where you replace the Year dimension with the ReportYear dimension.

• ###### Re: Comparison sum sales in Year with Previous Year

If you are ever going to select one year, then may be this:

=Before(Sum({<Year = {\$(=If(GetSelectedCount(Year) = 1, GetFieldSelections(Year)-1, Chr(39) & '*' & Chr(39)))}>}Sales))

• ###### Re: Comparison sum sales in Year with Previous Year

It's cool!!

It is exactly what we need.

Thank you, Sunny!

• ###### Re: Comparison sum sales in Year with Previous Year

That looks like a pivot table to me. If you want to do this in a straight table you'll have to create two expressions for each year that you want to show.

• ###### Re: Comparison sum sales in Year with Previous Year

Ok, it is a pivot table.

what I need to write in expression?

I have year in dimension and one expression is sum(sales) and second?

• ###### Re: Comparison sum sales in Year with Previous Year

Try sum(sales) and before(sum(sales))

• ###### Re: Comparison sum sales in Year with Previous Year

I attached small example