Need to pivot prior year data for set identified on the basis of current year data.

[#1]

Hi,

I’m struggling to figure out how to chart data for a prior year after applying a limit on the current year.  Essentially, I have a data set that has 10 years of donation behavior.  Each donor had a row for each of the last 10 years with an indicator for whether there is a gift that year, and another column of data that tells us how they gave.

So…

Donor Id         Year                Gift_Ind          Gift_Vehicle

111                  2014                N                     -

111                  2013                Y                     Online

111                  2012                Y                     Mail

111                  2011                Y                     Online

222                  2014                Y                     Online

222                  2013                Y                     In Person

222                  2012                N                     -

222                  2011                Y                     Online

333                  2014                N                     -

333                  2013                Y                     Mail

333                  2012                Y                     Mail                

333                  2011                Y                     In Person

I want to look at the set of people who have not given this year and see how they gave last year (online, via mail, etc…).  

So eventually, I want to identify the set of people for whom Year = 2014 & Gift_Ind = N, and then I want to know what their Gift Vehicle was when Year= 2013.   I want a pivot chart that looks like this:

Year                Last Gift Vehicle        Lapsed Donors

2014                Online                         1

                        Mail                             1

2012                Online                         1         

I believe Year and Gift Vehicle to both be dimensions and I’m just trying to work out the expression that will allow me to display the data for the year prior to my dimension year, but I’m having no luck. Any help is incredibly appreciated.

Thanks!

Grace

[JonnyPoole]

I hit on an approach that may work.  It seems to work for the limited data set but you will have to test more.

Need to do techniques in data model and chart to get it.

Let me know if it helps.

[JonnyPoole]

Kept thinking about this and I believe this new solution will be more flexible and easier to understand.

The work is done in the data load. I added a 'LapsedDonorCount' field that is 1 or 0 with the appropriate conditions to evaluate whether a donor lapsed. its dependent on the table being sorted in the data load which is why you see 'order by' in there.

Once built you can create the desired chart with Year, Prev_Gift_Vehicle as dimensions and  sum(LapsedDonorCount) as the expression.  Hope it works with your full data set. Let me know if it does not.

DataTemp:

LOAD [Donor Id],

     Year,

     Gift_Ind,

     Gift_Vehicle

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

NoConcatenate

Data:

load *,

     Previous(Gift_Vehicle) as Prev_Gift_Vehicle,

     Previous(Gift_Ind) as Prev_Gift_Ind,

     if( [Donor Id] = previous([Donor Id]) and Previous(Gift_Ind)='Y' and Gift_Ind='N' , 1,0) as LapsedDonorCount

resident DataTemp

order by [Donor Id],Year Asc;

drop table DataTemp;