3 Replies Latest reply: Jul 11, 2013 8:19 AM by konstantins kozins

# Expression formula based on dimension

Hello,

I have one problem to show data in the pivot table according to the dimension.

Amount:

[account, amount, Year Month

acc_1, 100, 201306

acc_2, 200, 201306

acc_3, 300, 201306

acc_4, 400, 201206

acc_5, 500, 201206

acc_6, 600, 201206];

If use the expressions for the current and previous years, the expression works properly.

TY : sum({<[Year Month] = {'201306'}>}amount)

PY : sum({<[Year Month] = {'201306'}>}amount)

But when I want to show the data with dimension (Actual, Last Year), the expression doesn't working for Last year

if(D1_no = 10, //Current Year

sum({<[Year Month] = {'201306'}>}amount),

if(D1_no = 30, //Last Year

sum({<[Year Month] = {'201206'}>}amount)

)

)

• ###### Re: Expression formula based on dimension

It works fine, if I will add NULL values for the missed periods for the accounts:

Concatenate(Amount)

[account, Year Month

acc_4, 201306

acc_5, 201306

acc_6, 201306

];

But I'm not sure that it's right way

• ###### Re: Expression formula based on dimension

Hi,

Remove the selection of YearMonth field.

Regards,

Kaushik Solanki

• ###### Re: Expression formula based on dimension

Problem that period should be selected