Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Calculation Logic

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 YearMetric2013201420152016
2013Original Donors25,000---
Original Sustainers25,00010,0007,0004,000
YOY Retention-40%70%57%
Base Retention-40%28%16%
2014Original Donors-40,000--
Original Sustainers-40,00015,0002,000
YOY Retention--37.5%13.3%
Base Retention--37.5%5%
2015Original Donors--65,000-
Original Sustainers--65,0005,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

1 Solution

Accepted Solutions
effinty2112
Master
Master

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 YearDonation Year2010201120122013201420152016
2010407341303272252245215
20111017361554949
20128161464435
201368524438
2014705653
20155532
201661

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

View solution in original post

15 Replies
Anonymous
Not applicable
Author

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

Not applicable
Author

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

ToniKautto
Employee
Employee

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
Not applicable
Author

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
Master
Master

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 YearYearOriginal DonorsOriginal SustainersYOY RetentionBase Retention
2013201325,00025,000
201410,000
20157,000
20164,000
20142013
201440,00040,000
201515,00037.50%37.50%
20162,00013.30%5%
20152013
2014
201565,00065,000
20165,000

Cheers

Andrew

Anonymous
Not applicable
Author

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
Master
Master

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 YearDonation Year2010201120122013201420152016
2010407341303272252245215
20111017361554949
20128161464435
201368524438
2014705653
20155532
201661

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
Not applicable
Author

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
Master
Master

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 YearDonation Year201120122013201420152016
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 YearDonation Year201120122013201420152016
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