LAST YEAR AND CURRENT YEAR

[inigoelv]

Hello,

I have a problem with the following table:

Year	MonthYear	Customer	Tons
2012	012012	A	100
2012	022012	B	200
2011	012011	A	300
2011	022011	B	400

I would like to create other table like the comparation between actual year and last year:

Year	Customer	2012	2011
2012	A	100	300
2012	B	200	400

I use the expression:

But in the column of Year appears 2012 and 2011, in diferent cells. i need the comparasion in the same line.

[#2]

Hi.

I created an example. I hope this can help you.

Alessandro Furtado

[inigoelv]

Sorry Alessandro:

I have the standar version and don´t let me to open it.

Could you put the expression used?

In addition to the example, the field Year is really Year (MonthYear). I forgot to say it.

Best regards,

[#4 · Accepted solution]

Hi.

You can create a graph object (table) and put the year as dimension and create expression like

Actual Year

sum({<Year={$(=max(Year))}>} Tons)

Last Year

sum({<Year={$(=max(Year -1))}>} Tons)

I hope this can help you.

Alessandro Furtado

[Anonymous]

Dear inigoelv,

      Please fiend the attachment of application and let you know.

Regards

[#6]

hi

this is exactly according to your requirment.

in script--

LOAD * INLINE [

    year, customer, tons

    2012, A, 100

    2012, B, 200

    2011, A, 300

    2011, B, 400

];

then add pivot chart--

          dimension---   customer

          expression---   (1)      sum({<year={$(=max(year))}>} tons)

                                (2)      sum({<year={$(=max(year -1))}>}tons)

then output like this--

customer	2012	2011
A	100	300
B	200	400

[inigoelv]

Hello:

Thanks but the problem is the field year is an expression:

Year=Year (MonthYear)

The reason is becuase I connect all the tables only with MonthYear.

If I use

sum ( {$<Year(MonthYear)= {$(#=Max(Year(MonthYear))-1)

it doesn´t work.

I prefer no create Year as key, becuase it complicate the script.

Best regards,

[inigoelv]

Hi Alessandro:

The answer is correct but I had to eliminate the Year (MonthYear) of the dimension of the chart, because if not appears in diferents lines:

2011	A	0	300
2011	B	0	400
2012	A	100	0
2012	B	200	0

No choice.

Thanks anyway