18 Replies Latest reply: Jul 10, 2017 11:12 AM by Weston Kingsley RSS

    Set Analysis with Multiple ANDs

    Weston Kingsley

      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!

        • Re: Set Analysis with Multiple ANDs
          Mark Ritter

          To make things a simple as possible in the app I would create these flags in your load script.

           

          2015Donated Flag and 2016DonatedFlag

           

          Then in the app you don't have worry about anything but checking these 2 flags.  No complicated Set Analysis.

          • Re: Set Analysis with Multiple ANDs
            Manish Kachhia

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

            =COUNT({<DonorID = p({1<AssignedFlag = {'Y'}, GiftYear = {'$(=Year(Today())-2)'}>}DonorID)>}DISTINCT DonorID)


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

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

              • Re: Set Analysis with Multiple ANDs
                Weston Kingsley

                Thank you Manish, this seems to be working!

                 

                Can you explain the use of "p" and the "1" before each set?

                  • Re: Set Analysis with Multiple ANDs
                    Manish Kachhia

                    Check below link.

                    Set modifiers with implicit field value definitions ‒ QlikView

                    Try to understand the use of P() and E() from this link.

                    This is also called as Indirect Set Analysis.

                     

                    DonorID = P(DonorID)

                    Mean Possible DonorID


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

                    Here Possible DonorID which are having AssignedFlag = Y and GiftYear 2 years back compare to today's year

                     

                    1 is used to ignore the filters users will do in application, so that the end result would not get affected.

                     

                    Regards,

                    MK

                      • Re: Set Analysis with Multiple ANDs
                        Weston Kingsley

                        mrkachhiaimp Thanks for the links on this. This helped my understanding quite a bit after seeing it applied to this solution, so thanks!

                         

                        As a follow up question, now that I have these 2 list of Donors with the qualifications, I'm trying to figure out what the Money lost is from 2015 to 2016. The difference between Calc 1 and Calc 2 is what we're calling "lapsed donors". If I subtract Calc 2 from Calc 1 I get the list of Donors who are assigned that gave in 2015 but NOT 2016. I'd like to find out how much money we lost  assuming they would've given the same amount.

                         

                        Does this make sense? How would I go about calculating this?

                          • Re: Set Analysis with Multiple ANDs
                            Manish Kachhia

                            Provide sample data here.. I will try to give you expressions for that.


                              • Re: Set Analysis with Multiple ANDs
                                Weston Kingsley

                                Sure.

                                 

                                Donors

                                DonorIDAssignedFlagTotal2015Total2016

                                001

                                Y500010000
                                002Y100000
                                003N250005000
                                004N50000
                                005Y20000
                                006Y2000010000
                                007Y05000

                                 

                                Gifts

                                GiftIDDonorIDGiftDateGiftAmount
                                1010011/1/2015

                                5000

                                1020011/1/201610000
                                1030022/1/201510000
                                1040033/1/201525000
                                1050033/1/20165000
                                1060044/1/20155000
                                1070055/1/20162000
                                1080066/1/201520000
                                1090066/1/201610000
                                1100077/1/20165000

                                 

                                With this data, the donors we would "lose" money from are 002 and 005 because they gave in 2015 but not in 2016 (Notice Donor 004 also gave in 2015, not in 2016, but isn't Assigned). The total money lost from these two Donors, if they would've given the same amount HAD they given, would be $12,000 ($2,000 for Donor 005 + $10,000 from Donor 002).

                                 

                                Thanks for taking a look!

                                  • Re: Set Analysis with Multiple ANDs
                                    Manish Kachhia

                                    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)

                                      • Re: Set Analysis with Multiple ANDs
                                        Weston Kingsley

                                        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?


                                          • Re: Set Analysis with Multiple ANDs
                                            Manish Kachhia

                                            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)

                                              • Re: Set Analysis with Multiple ANDs
                                                Weston Kingsley

                                                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?

                                                  • Re: Set Analysis with Multiple ANDs
                                                    Manish Kachhia

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

                                                      • Re: Set Analysis with Multiple ANDs
                                                        Weston Kingsley

                                                        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;

                                  • Re: Set Analysis with Multiple ANDs
                                    Manish Kachhia

                                    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)