topic Re: Prior Year...on the same row in QlikView

Prior Year...on the same row

randy_kluver — Thu, 21 Jun 2018 17:55:07 GMT

I'm trying to show in a straight table, how one year compares to the prior year. And calculate growth in one line.

given the following example (Attached) here is what I'm trying to achieve

Year	Amount	Prior Year Amount (Not working)
2015	100	95
2016	150	100
2017	165	150
2018	175	165

I thought it would be simple enough to say... SUM({<[Year] = {'=([Year]-1)'}>} Amount)

Example workbook attached.

What am I missing?

Thanks,

Randy

Message was edited by: Randy Kluver Corrected my file... should have data for 2014, but the chart would not show2014, except as the amount in Prior Year for 2015.

Re: Prior Year...on the same row

sunny_talwar — Thu, 21 Jun 2018 18:28:24 GMT

Try this

Above(Sum(VALUE))

Re: Prior Year...on the same row

randy_kluver — Thu, 21 Jun 2018 19:10:25 GMT

Quick Update....

in the Data... There would be data for 2014... So the row for 2015 should have a Prior year value in it. (Sorry for not clarifying that earlier).

Should look like this:

DATA:

2014 95

2015 100

...

Chart... Will not Show 2014, but still want to show the Prior Year in 2015

Year Current Year Prior Year

2015 100 95

Re: Prior Year...on the same row

sunny_talwar — Thu, 21 Jun 2018 19:14:34 GMT

Try this

1) Current Year

Sum({<YEAR = {">=2015"}>} VALUE)

2) Prior Year

Above(Sum({<YEAR>}VALUE)) * Avg(1)

Re: Prior Year...on the same row

randy_kluver — Thu, 21 Jun 2018 20:48:39 GMT

Sunny,

Expanding on that that solution.

I tested and verified that I can use the OFFSET in the ABOVE function to adapt it to fit for using Quarters.

My next question is....

Is there a safeguard to make sure I'm comparing the right Years or Quarters?

Example:

Given Years 2014 - 2019.... a User excludes 2017.

Using ABOVE() would not compensate for that. The solution noted would for 2018 show Prior Year = 2016; not 2017.

Is there a solution using Set Analysis that would ensure or calculate which Year value to return?

Thanks,

Randy

Re: Prior Year...on the same row

sunny_talwar — Fri, 22 Jun 2018 02:49:50 GMT

The best way might be to use The As-Of Table to handle this.

Re: Prior Year...on the same row

karthikoffi27se — Tue, 07 Jan 2020 10:28:13 GMT

Hi Sunny,

Thanks for the solution much appreciated. I have modified your expression and used it, It's working when I inlcude just year wise like mentioned in the image below

Previous year data:- Above(Sum({<POLICY_UW_YEAR, PAYTYPE={'PREMIUM'}>}AMOUNT)) * Avg(1)

In my case, I have these many fields in my pviot table, How do I approch, can you help me please

POLICY NO

RENNO

POLICY_TYPE

CUST_TYPE

INSURED

CUSTOMER_AGE

PERIOD_FROM

PERIOD_TO

PRODUCT

MAKE

MODEL

MANFYEAR

SEATS

CYLINDER

REG_NO

BODY TYPE

REPAIR_COND

CHASISNO

TRAFFIC_NO

MOBILE_NO

USER_NAME

CHANNEL_NAME

SOURCE_SYSTEM