17 Replies Latest reply: Feb 23, 2012 3:02 AM by Paul Damen

# Compare to last year in table

Hello all,

I probably have a pretty simple question but I can' figure it out. I have a simple table in which I have several figures which are calculated like this:

=sum({<cost_center_function = {'Sales'}> [Amount_actual] ).

Which in this case gives me the sales amount. Now I want to add a column with previous year. So if I select 2012 I automatically want to see 2011 in that column the same if I select 1 month, so if I select jan 2012, I automatically want to see jan 2011 etc.

I tried things like month-12 etc. but that doesn't work.

Hope you guys can help me!

Regards,

Paul

• ###### Re: Compare to last year in table

Hi

Use the expression like this,

Where SetDate = if(GetFieldSelections(Year)>0 OR GetFieldSelections(Month) > 0 ,MonthStart(MakeDate(Max(Year),Max(Month))),Today())

Setdate default shows the Current Date (21-02-2012)

Regards,

Iyyappan

• ###### Compare to last year in table

And were do I put this, in the expression? Because it doesn't recongnize it.

And how do I put in my selection (costcenter = sales and amount_actual)?

• ###### Re: Compare to last year in table

Hi,

use like this

Set the Variable ' AIT + CTRL -->SetDate = if(GetFieldSelections(Year)>0 OR GetFieldSelections(Month) > 0 ,MonthStart(MakeDate(Max(Year),Max(Month))),Today())

Regards,

Iyyappan.

• ###### Compare to last year in table

Thanks were getting there, only it doesn't recognize the end part so the amount_actual. It says expression ok, but the amount actual is not recognized as a field.

• ###### Compare to last year in table

Hi,

Try with this

=sum({<cost_center_function = {'Sales'},YearField={\$(=Max(YearField)-1)}> [Amount_actual] )

Celambarasan

• ###### Compare to last year in table

Hi,

Try using [ ] for the Field/Dimension name.

Regards,

jagan.

• ###### Compare to last year in table

Thanks again for the reply, it gives me a number now but it doesn't change. So the amount in 2011 is 1.000.000 but if I change to 2012 it still shows 1.000.000 no matter what selection I make it keeps showing that amount.

• ###### Compare to last year in table

Hi,

Try this

Regards,

Jagan.

• ###### Compare to last year in table

Now it changes but gives the same number as the current selection so not the same selection but a year ago.

• ###### Re: Compare to last year in table

Hi,

can u please attach ur file.

Regards,

Iyyappan.

• ###### Compare to last year in table

Sorry I can't attach the file. Classified info...

• ###### Compare to last year in table

Hi,

Any thoughts why it now only shows me the same period (so not a year back)?

Regards,

Paul

• ###### Compare to last year in table

Hi,

This will show the Previous Year value, because we are using "AddYears((SetDate),-1))", this will get you the previous years data.

This expression is used for current year data

=Sum({<costcenter= {'Sales'},SetDate={">=\$(=MonthStart((SetDate))) <\$(=MonthEnd((SetDate)))"}>}[amount_actual])

Hope this helps you.

Regards,

Jagan.

• ###### Compare to last year in table

Hi,

Thanks again for all your help. But it still returns the same numbers in each column, so still not looking a year back....

• ###### Compare to last year in table

Hi,

Can you attach the sample file with sample data. It would be helpful in understanding the problem.

Regards,

Jagan.

• ###### Compare to last year in table

I figured it out! Was something in my date table why it wasn't going correctly, but I solved it by adding an other variable.

Thanks a lot for your help!

• ###### Re: Compare to last year in table

Hi,

Use the below expression to get previous year values

=sum({<YearFieldName = {'=\$(=Max(YearFieldName) - 1)'}, cost_center_function = {'Sales'}>} [Amount_actual] )

Replace YearFieldName with you Year Field.

Hope this helps you.

Regards,

Jagan.