Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Pick the year before

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

1 Solution

Accepted Solutions
autopilot
Contributor III

Re: Pick the year before

Use set analysis.

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

5 Replies
Not applicable

Re: Pick the year before

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.

Not applicable

Re: Pick the year before

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

Not applicable

Re: Pick the year before

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.

autopilot
Contributor III

Re: Pick the year before

Use set analysis.

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

Not applicable

Re: Pick the year before

Thanks it worked.

Community Browser