3 Replies Latest reply: Oct 12, 2011 8:34 PM by Sokkorn Cheav

# Comparision of selection against last year

Hi.

I'm sure there is a simple solution to my issue, but my mind really will not help me out today, and as I haven't been looking too much into set analysis yet, I'm not sure how to write the correct expression.

From my database, I have one separate column for month, and one for year, so no specific date column (the way it is presentet to me in the first place, not a good choice, but that is another issue.)

I also have a column named "x" which contains numeric data. What I want to do, is comparing the values in column "x" with the values for the past year.

So the expression would be somewhat like:

1-Sum(x)/Sum(x)

and formatted to be displayed in %.

But I also need to state that it shall be:

1-Sum(x)/Sum(x) where "Year" = selection - 1.

Good ideas/a solution to this issue would be appreciated.

/Jonas

• ###### Re: Comparision of selection against last year

Hi Jonas,

SUM({<[Year]={\$(=Max([Year]))}>}[Amt]) ==> Will return [current selection year]

SUM({<[Year]={\$(=Max([Year]-1))}>} [Amt]) ==> Will return [current selection year] - 1

Regards,

Sokkorn

• ###### Re: Comparision of selection against last year

Hi Sokkorn,

So my total expression ends up looking like:

1-Sum({[Year]=(\$((=Max([Year]-1))}>[x])/Sum({[Year]=(\$((=Max([Year]))}>[x])

And this returns exactly what I was after when displaying the numbers in %

Thank you again for your quick reply, and have a good day.

Regards,

Jonas

• ###### Re: Comparision of selection against last year

Hi Jonas,

I'm not clear what you want. But I thinks that you need to show result in percentage. If so that, please take a look my formular below

NUM(1-Sum({[Year]=(\$((=Max([Year]-1))}>[x])/Sum({[Year]=(\$((=Max([Year]))}>[x]),'0.00%')

Regards,

Sokkorn