# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for
Did you mean:
Highlighted
Creator

## 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:

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

15 Replies
Highlighted
Specialist

Highlighted
Not applicable

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

Highlighted
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?

Highlighted
Creator

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?

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

Highlighted
Creator

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.

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

Highlighted
Creator

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.

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