topic Re: Pick the year before in QlikView

Pick the year before

Wed, 02 Jan 2013 16:12:06 GMT

I am using a straight table that is setup to show Sales Data by customer. I want to do a comparison to last year. So if 2012 is choosen as a filter, I want a column to show sales in 2011. How can I get it to know to subtract a year in that column.

Thanks!!!

Re: Pick the year before

Wed, 02 Jan 2013 17:06:45 GMT

Dear ljames77,

you can do it by creating a variable for maxyear.

kindly find the attachement of sample qvw

i hope it will help you.

Thanks,

Mukram.

Re: Pick the year before

Wed, 02 Jan 2013 17:48:21 GMT

I have a trial version of qlikview so I cannot open your document, any way you can send over the script in an email?

Lance H. James, CPA

Director of Finance

The Aspen Brands Company

Kate Aspen · Baby Aspen

678.282.0053 x 777

<http://www.theaspenbrands.com/>

<http://www.kateaspen.com/>

<http://www.babyaspen.com/>

Re: Pick the year before

Wed, 02 Jan 2013 17:59:22 GMT

Dear ljames77,

script:

Sales:

LOAD * Inline

[

Country,Sales,Year

US,24000,2010

US,25000,2011

US,10000,2012

UK,40000,2011

UK,50000,2012

SA,1200,2009

SA,15000,2010

SA,1000,2011

SA,25000,2012

]

;

First create a variable vMaxYear=Max(Year)

create a stright table with following Expressions:.

first expression:

for current Year

=sum({<Year={'$(=VMaxYear)'}>}Sales)

second expression:

for prevoius Year

=sum({<Year={'$(=VMaxYear-1)'}>}Sales)

Thrid expressions:

diffrence beween the above two year

=Column(1)-Column(2)

Thanks,

Mukram.

Re: Pick the year before

Anonymous — Wed, 02 Jan 2013 18:04:42 GMT

Use set analysis.

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

Re: Pick the year before

Wed, 02 Jan 2013 18:56:05 GMT

Thanks it worked.