Prior Year...on the same row

[randy_kluver]

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.

[sunny_talwar]

Try this

Above(Sum(VALUE))

[randy_kluver]

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

[sunny_talwar · Accepted solution]

Try this

1) Current Year

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

2) Prior Year

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

[randy_kluver]

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

[sunny_talwar]

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

[karthikoffi27se]

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

EMAIL

USER_NAME

CHANNEL_NAME

SOURCE_SYSTEM