Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

pauldamen
Contributor 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
Valued Contributor

Re: Compare to last year in table

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
Contributor II

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

v_iyyappan
Valued Contributor

Re: Compare to last year in table

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.

MVP
MVP

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.

pauldamen
Contributor II

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

MVP
MVP

Compare to last year in table

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
Contributor II

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.

MVP
MVP

Compare to last year in table

Hi,

Try this

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

Regards,

Jagan.

Community Browser