Compare to last year in table

[pauldamen]

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

[v_iyyappan]

Hi

     Use the expression like this,

Sum({<Year=,Month=,SetDate={">=$(=MonthStart(AddYears((SetDate),-1))) <$(=MonthEnd(AddYears((SetDate),-1)))"}>}Sales)

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

[pauldamen]

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

[v_iyyappan]

Hi,

     use like this

=Sum({<costcenter= {'Sales'},Year=,Month=,SetDate={">=$(=MonthStart(AddYears((SetDate),-1))) <$(=MonthEnd(AddYears((SetDate),-1)))"}>}amount_actual)

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

Regards,

Iyyappan.

[jagan]

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.

[pauldamen]

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.

[CELAMBARASAN]

Hi,

     Try with this

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

Celambarasan

[jagan]

Hi,

Try using [ ] for the Field/Dimension name.

=Sum({<costcenter= {'Sales'},Year=,Month=,SetDate={">=$(=MonthStart(AddYears((SetDate),-1))) <$(=MonthEnd(AddYears((SetDate),-1)))"}>}[amount_actual])

Regards,

jagan.

[pauldamen]

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.

[jagan]

Hi,

Try this

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

Regards,

Jagan.