Comparision of selection against last year

[#1]

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.

Thank you in advance,

/Jonas

[Sokkorn · Accepted solution]

Hi Jonas,

I think this will help you to solve your problem

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

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

Regards,

Sokkorn

[#3]

Hi Sokkorn,

thank you for your reply.

So my total expression ends up looking like:

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

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

[Sokkorn]

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))}>)/Sum({[Year]=($((=Max([Year]))}>),'0.00%')

Regards,

Sokkorn