topic Re: LAST YEAR AND CURRENT YEAR in QlikView

LAST YEAR AND CURRENT YEAR

inigoelv — Sun, 27 Jan 2013 01:17:27 GMT

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.

Re: LAST YEAR AND CURRENT YEAR

Sun, 27 Jan 2013 02:00:24 GMT

Hi.

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

Alessandro Furtado

Re: LAST YEAR AND CURRENT YEAR

inigoelv — Sun, 27 Jan 2013 09:12:07 GMT

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,

Re: LAST YEAR AND CURRENT YEAR

Sun, 27 Jan 2013 12:55:02 GMT

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

Re: LAST YEAR AND CURRENT YEAR

Anonymous — Sun, 27 Jan 2013 17:24:43 GMT

Dear inigoelv,

Please fiend the attachment of application and let you know.

Regards

Re: LAST YEAR AND CURRENT YEAR

Mon, 28 Jan 2013 05:54:25 GMT

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

Re: LAST YEAR AND CURRENT YEAR

inigoelv — Mon, 28 Jan 2013 21:28:43 GMT

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,

Re: LAST YEAR AND CURRENT YEAR

inigoelv — Sat, 02 Feb 2013 20:23:12 GMT

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