Pick the year before

[#1]

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!!!

[#2]

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.

[#3]

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/>

[#4]

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.

[Anonymous · Accepted solution]

Use set analysis.

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

[#6]

Thanks it worked.