Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)?
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.
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.
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.
Hi,
Try with this
=sum({<cost_center_function = {'Sales'},YearField={$(=Max(YearField)-1)}> [Amount_actual] )
Celambarasan
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.
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.
Hi,
Try this
=Sum({<costcenter= {'Sales'},SetDate={">=$(=MonthStart(AddYears((SetDate),-1))) <$(=MonthEnd(AddYears((SetDate),-1)))"}>}[amount_actual])
Regards,
Jagan.