Calculation Logic

[Anonymous]

Hello QC!

Need some help with a Pivot Table today. My logic/SQL is a bit rusty (haven't done development in a while) so I'd appreciate the help tremendously.

I am creating a table that has 11 different metrics/calculations grouped by years (2013-2016, see column 1), and also measured across years (need years on horizontal and vertical rows). The calculations is to provide metrics about Donor Data for our non-profit for the last 4 years.

There are two metrics that are needed for most of the others that I need help calculating.

1) Original Donors - [OD] - The number of Donors whose first gift was given in 2013 (or selected [First Gift Year]).

2) Original Sustainers [OS] - The number of Donors that gave in a year (2013-2016) that became first time Donors in 2013 ([OD]). So in this case the value for [OS] in 2013 will equal the value for [OD] in 2013; Same for other years.

Any thoughts on how to write these Count/If statements?

It hopefully will look something like this:

First Gift Year	Metric	2013	2014	2015	2016
2013	Original Donors	25,000	-	-	-
	Original Sustainers	25,000	10,000	7,000	4,000
	YOY Retention	-	40%	70%	57%
	Base Retention	-	40%	28%	16%
2014	Original Donors	-	40,000	-	-
	Original Sustainers	-	40,000	15,000	2,000
	YOY Retention	-	-	37.5%	13.3%
	Base Retention	-	-	37.5%	5%
2015	Original Donors	-	-	65,000	-
	Original Sustainers	-	-	65,000	5,000

As usual, if this isn't clear, or you need more info, let me know. My SQL needs polishing, so any help is appreciated!

Thanks in advance to you Qlik Wizards!

-misterk

[Anonymous]

It seems you could use Group By in your query when loading data from data.

[#3]

I am not sure how the raw data looks like. The table can be constructed using pivot in qlikview directly too.

[ToniKautto]

It is great that you show the expected result. The way to accomplish this depends on what you loaded data looks like. Can you provide the loaded data for the example result you provided?

[Anonymous]

Sure.

Basically I'm bringing in three tables from our database that each have a ton of fields: Donors, Gifts, and Gift Codes. This question only is involving Donors and Gifts.

What is the best way to communicate that here? Just showing the schema and the data format within the tables?

[effinty2112]

Hi Weston,

               What about your data being presented like this, a pivot table with dimensions [First Gift Year] and Year plus four expressions?:

First Gift Year	Year	Original Donors	Original Sustainers	YOY Retention	Base Retention
2013	2013	25,000	25,000
	2014		10,000
	2015		7,000
	2016		4,000
2014	2013
	2014	40,000	40,000
	2015		15,000	37.50%	37.50%
	2016		2,000	13.30%	5%
2015	2013
	2014
	2015	65,000	65,000
	2016		5,000

Cheers

Andrew

[Anonymous]

Hi Andrew, thanks for the response.

The format for me isn't all that important, so your suggestion is certainly valid and I'm open to changing up a little how it looks. The current presentation (above) is how our current report looks that a third party developed that our internal managers are used to seeing; so we thought we would model is similarly for their ease.

I need help with the actual calculations of the data. I've read up on Counts and nested ifs and how QV uses various functions, but I can't seem to get the calculations correct. I'm not able to answer these questions:

"How many Donors gave [this selected year] for the first time?"

"How many Donors gave [this selected year] that gave their first gift in [that year]?"

I just used random data for my example table. The mathematical functions are relatively simple, but the group and data aggregation/set analysis is what's getting me tripped up.

[effinty2112 · Accepted solution]

Hi Weston,

                  Are you looking for something like this, giving the number of donors by year of first donation and donation year. So here 407 donors made their first donation in 2010 and of those 341 made a donation in 2011, 303 in ... etc.?

First Year	Donation Year	2010	2011	2012	2013	2014	2015	2016
2010		407	341	303	272	252	245	215
2011			101	73	61	55	49	49
2012				81	61	46	44	35
2013					68	52	44	38
2014						70	56	53
2015							55	32
2016								61

If so the expression is just Count(DISTINCT DonorID). If you have [First Year] of donation as a field in your Donor table then great, if not a calculated dimension would do the job, something like : =Aggr(Min(Year([Donation Date])), DonorID)

Cheers

Andrew

[Anonymous]

This worked perfectly.

I actually made the mistake (over and over again) of bad field name that weren't representing accurate things.

So between your response and calculations, I saw one of my problems and everything worked as it should.

Thanks Andrew! you are a wizard indeed!

Cheers.

[effinty2112]

Hi Weston,

If you use this expression you'll get the YonY change:

if(Before(count(DISTINCT DonorID))>0,

Count(DISTINCT DonorID)-Before(count(DISTINCT DonorID)))

First Year	Donation Year	2011	2012	2013	2014	2015	2016
2010		-66	-38	-31	-20	-7	-30
2011			-28	-12	-6	-6
2012				-20	-15	-2	-9
2013					-16	-8	-6
2014						-14	-3
2015							-23

For % change:

if(Before(count(DISTINCT DonorID))>0,

(Count(DISTINCT DonorID)-Before(count(DISTINCT DonorID)))/Before(count(DISTINCT DonorID)))

First Year	Donation Year	2011	2012	2013	2014	2015	2016
2010		-16.22%	-11.14%	-10.23%	-7.35%	-2.78%	-12.24%
2011			-27.72%	-16.44%	-9.84%	-10.91%
2012				-24.69%	-24.59%	-4.35%	-20.45%
2013					-23.53%	-15.38%	-13.64%
2014						-20.00%	-5.36%
2015							-41.82%

Good luck

Andrew