topic Re: Calculation Logic in QlikView

Calculation Logic

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculation Logic

Anonymous — Wed, 31 Aug 2016 03:09:37 GMT

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

Re: Calculation Logic

Wed, 31 Aug 2016 06:05:16 GMT

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

Re: Calculation Logic

ToniKautto — Wed, 31 Aug 2016 13:21:02 GMT

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?

Re: Calculation Logic

Anonymous — Wed, 31 Aug 2016 13:29:20 GMT

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?

Re: Calculation Logic

effinty2112 — Wed, 31 Aug 2016 13:39:10 GMT

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

Re: Calculation Logic

Anonymous — Wed, 31 Aug 2016 13:52:24 GMT

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.

Re: Calculation Logic

effinty2112 — Wed, 31 Aug 2016 15:33:23 GMT

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

Re: Calculation Logic

Anonymous — Wed, 31 Aug 2016 15:56:27 GMT

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.

Re: Calculation Logic

effinty2112 — Wed, 31 Aug 2016 16:05:20 GMT

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

Re: Calculation Logic

Anonymous — Wed, 31 Aug 2016 19:43:25 GMT

Thanks for the great stuff.

What about calculating Base Year Retention/Change?

So using the First Year instead of the Previous Year? I tried using first, but once I get to 2014, it still grabs 2013.

Does that make sense?

Re: Calculation Logic

effinty2112 — Wed, 31 Aug 2016 20:26:25 GMT

Hi Weston,

Try this for your expression:

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

Count(DISTINCT DonorID)-Before(count(DISTINCT DonorID),ColumnNo()-RowNo()))

Cheers

Andrew

Re: Calculation Logic

Anonymous — Wed, 31 Aug 2016 20:37:24 GMT

I actually need the Percent Retention not the change...my mistake.

Re: Calculation Logic

effinty2112 — Wed, 31 Aug 2016 20:58:12 GMT

We're getting there Weston, we're getting there!

Try:

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

Count(DISTINCT DonorID)/Before(count(DISTINCT DonorID),ColumnNo()-RowNo()))

Cheers

Andrew

Re: Calculation Logic

Anonymous — Wed, 31 Aug 2016 21:00:35 GMT

Beautiful. Works great,

Do you mind explaining how the before uses the col/row functions? It's a bit unclear even after reading some of the documentation on it.

Re: Calculation Logic

effinty2112 — Wed, 31 Aug 2016 21:41:26 GMT

Hi Weston,

Very happy it's working for you. Will try to explain but please forgive me if I don't reply until tomorrow, it's 10:40 pm for me and I need my beauty sleep.

all the best

Andrew