Table with data comparation

[#1]

Dear all I have to create a table with comparation between months and I have no Idea how to do that.

I have a table with a structure like

Date Sales

01/01/2009 20

02/01/2009 40

02/02/2009 50

.....

I have a system variable with a date and a want to do.

From the year of that variable obtain the next table.

Year

Mont of the data Sales

Example

01/01/2009 10

01/02/2009 2

01/02/2009 3

01/03/2009 5

2009

January

February -50%

March 0 %

....

And in every month diference with the previous in %.

Can someone help me with the code of the expressions / dimensions.

Best regards

[sparur]

If i Understand your question, that this expression can help you:

sum(Sales) / above(sum(Sales))

dimension by Month

[justinasp]

Try this:

test1:
LOAD * INLINE
[
yearmonth,sales
201001,10
201002,3
201002,2
201003,5
];
test2:
LOAD yearmonth,
 sum(sales) as msales
RESIDENT test1
GROUP BY yearmonth
ORDER BY yearmonth;
test3:
LOAD yearmonth,
 msales,
 (msales-numsum(peek(msales)))/numsum(peek(msales)) as percentage
RESIDENT test2;
DROP TABLE test2;

Cheers,

Justinas

[#4]

OK Thanks,

that's the answer.

But only one more thing...

How to compare the same month on diferent years.

Best regards

[#5]

Hi sparur. That's the solution, but.... How can I do to compare same month on diferent years.

Best regards.

[Miguel_Angel_Baeyens]

Hello Jordi,

Given that Year is a dimension showed in a column in your table, and "Sales" the expression you use to calculate monthly sums, and years ordered higher to lower, you can do

([Sales] - after([Sales])) / after([Sales])

After() function will take next column's value.

Hope that helps.