Set Analysis with Multiple ANDs

[Anonymous]

Hello Qlik Experts,

I have two (maybe three if necessary) tables I'm looking at for this one, Donors and Gifts:

Donor:

DonorID,

AssignedFlag (this is a Y or N)

Gift:

GiftID,

GiftDate,

DonorID

(An optional third is a Gift Calendar😞

GiftDate,

Year(GiftDate) as GiftYear,

Month(GiftDate) as GiftMonth

I need to Count the set of Donors who are Assigned that gave a gift in 2015, and compare that to Donors that were assigned that gave a gift in 2015 AND in 2016.

Calc 1 = Donors with a 'Y' in AssignedFlag AND who have a Gift Date in 2015

Currently I'm using this calculation:

Count({$<[Assigned Flag]={'Y'},[Gift Year]={$(=Year(Today())-2)}>}Distinct [Donor ID])

This seems to be working correctly, but would appreciate feedback if I'm missing something...

Calc 2 = Donor with a 'Y' in AssignedFlag AND who have a Gift Date in 2015 AND who have a Gift Date in 2016

This is the Calculation I'm having trouble getting correct and am looking for help on.

Basically we're trying to calculate who gave a gift 2 years ago and of those donors, who gave again in 2016.

Thanks in advance for the help!

[MK_QSL]

Try this

=SUM(

{< GiftYear = {'$(=Year(Today())-2)'},

AssignedFlag = {'Y'},

DonorID = P({1<GiftYear = {'$(=Year(Today())-2)'}>}DonorID)*E({1<GiftYear = {'$(=Year(Today())-1)'}>}DonorID)

>}

GiftAmount)

or

=SUM(

{< GiftYear = {'$(=Year(Today())-2)'},

AssignedFlag = {'Y'},

DonorID = P({1<AssignedFlag = {'Y'},GiftYear = {'$(=Year(Today())-2)'}>}DonorID)*E({1<AssignedFlag = {'Y'},GiftYear = {'$(=Year(Today())-1)'}>}DonorID)

>}

GiftAmount)

[Anonymous]

This worked perfectly! Thank you.

Now I'm just trying to modify it to add another calculation to add up the money lost from people who DOWNGRADED or logically, Gave Less in 2016 than 2015, but not working yet.

Based on the tables above, the result would be $22,000. Donors 002, 005, and 006 all are assigned AND gave less in 2016 than 2015.

The greater than expressions so far haven't worked. How do I compare the two values, then sum the differences to see the money lost?

[MK_QSL]

I think it should be 32,000

=SUM(

{< GiftYear = {'$(=Year(Today())-2)'},

AssignedFlag = {'Y'},

DonorID = {"=SUM({<GiftYear = {'$(=Year(Today())-1)'}>}GiftAmount)<SUM({<GiftYear = {'$(=Year(Today())-2)'}>}GiftAmount)"}

>}

GiftAmount)

[MK_QSL]

Or use this

=SUM(

{< GiftYear = {'$(=Year(Today())-2)'},

AssignedFlag = {'Y'},

DonorID = {"=SUM({<GiftYear = {'$(=Year(Today())-1)'}>}GiftAmount)<SUM({<GiftYear = {'$(=Year(Today())-2)'}>}GiftAmount)"}

>}

GiftAmount)

-

SUM(

{< GiftYear = {'$(=Year(Today())-1)'},

AssignedFlag = {'Y'},

DonorID = {"=SUM({<GiftYear = {'$(=Year(Today())-1)'}>}GiftAmount)<SUM({<GiftYear = {'$(=Year(Today())-2)'}>}GiftAmount)"}

>}

GiftAmount)

[Anonymous]

I'm still seeing $22,000.

Donors 001 and 007 increased their giving.

Donors 003 and 004 are not assigned.

That leaves 002 ($10,000 decrease), 005 ($2,000 decrease), and 006 ($10,000) . Did I miss something?

Just want to make sure the calculation is right, so if the expression you mentioned returns 32,000 then must be off, right?

[MK_QSL]

Check my 2nd expression, which is giving 22,000

[Anonymous]

When I use the first script I get $30,000 and when I use the second I get $20,000. It looks like it's not including Donor 005 when I use selections.

Is there a way to do this using the Totals in the Donor table instead?

Here's the load I'm using, there could be a mistake there (I've tried it with and without the date function):

Donors:

Load * Inline [

DonorID,AssignedFlag,Total2015,Total2016

001,Y,5000,10000

002,Y,10000,0

003,N,25000,5000

004,N,5000,0

005,Y,2000,0

006,Y,20000,10000,

007,Y,0,5000

]

;

Gifts:

Load GiftID,DonorID,Date(GiftDate) as GiftDate,GiftAmount Inline [

GiftID,DonorID,GiftDate,GiftAmount

101,001,'1/1/2015',5000

102,001,'1/1/2016',10000

103,002,'2/1/2015',10000

104,003,'3/1/2015',25000

105,003,'3/1/2016',5000

106,004,'4/1/2015',5000

107,005,'5/1/2016',2000

108,006,'6/1/2015',20000

109,006,'6/1/2016',10000

110,007,'7/1/2016',5000

]

;

GiftCalendar:

Load

GiftDate,

    Year(GiftDate) as GiftYear,

    Month(GiftDate) as GiftMonth

Resident Gifts;

[MK_QSL]

107,005,'5/1/2016',2000

This must be 5/1/2015

Date looks wrong to me..

[Anonymous]

Ah, I missed that. Numbers were running together.

Thanks! expression is verified to be working. Excellent work.

Can you explain what each expression is doing?

From what I can tell the first equation is Summing the total gift amount from 2015 where donors gave more in that year than 2016. The second looks to do the same thing, then subtracts the amount given in 2016.

Is this correct?