Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pauldamen
Partner - Creator II
Partner - Creator II

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

17 Replies
v_iyyappan
Specialist
Specialist

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
Partner - Creator II
Partner - Creator II
Author

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
Specialist
Specialist

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
Luminary Alumni
Luminary Alumni

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
Partner - Creator II
Partner - Creator II
Author

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
Partner - Champion
Partner - Champion

Hi,

     Try with this

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

Celambarasan

jagan
Luminary Alumni
Luminary Alumni

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
Partner - Creator II
Partner - Creator II
Author

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
Luminary Alumni
Luminary Alumni

Hi,

Try this

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

Regards,

Jagan.