8 Replies Latest reply on Nov 9, 2015 10:53 AM by Rody Zakovich

    Trying to get a COUNTD across a rolling span of months

    Joe Oppelt

      Attached is a sample workbook showing how far I've gotten with this.  (9.0 packaged workbook.)

       

      It's a running FYTD by donation amount.  Within that data is also a field that shows who donated.  ([Constituent Definition Rollup Id]).

       

      In the tooltips I have a COUNTD for donors within the month.  Works correctly.  I also want a running COUNTD from beginning of the fiscal year through the current month for donors.  (I also threw that number on the label above the bar for now.)

       

      So for the first month we have 96 unique donors.  Month 2 has 84 unique donors for the month, but there are donors who gave in both months, and I want the COUNTD across those two months.  (If you look at Sheet2, the COUNTD for the two months in that viz is 173, and the sum of the two COUNTD values on the first sheet is 180.)

       

      I need to figure out how to grab 173, not 180.  And in month3 I need to show 288 (mess with Sheet2 to arrive at that number), not 304 (which is the sum of the three values of COUNTD by month).

       

      You can see some of the various attempts I made at figuring this out.  Look in [Running sum countd Constituent Definition Rollup] table calc at the commented lines.  There might be a LOD calc, but I haven't made that work either.

       

      The small numbers I have on the labels over the bars on the first sheet are various values I was trying to use in calc-ing this.  So they're not part of the end viz.

       

      I'm missing something that I should be seeing, and I could use another set of eyes to help me find it.

        • 1. Re: Trying to get a COUNTD across a rolling span of months
          Shine Pulikathara

          Have you looked at this thread? Seems like a similar discussion, perhaps you can find some pointers there.

          • 2. Re: Trying to get a COUNTD across a rolling span of months
            Joe Oppelt

            Shine -- Thanks for the pointer.

             

            When I first opened that thread and I saw dates on the posts from late July, I was confused about how I could have missed such a recent thread...


            Then I saw the 2012 dates. 

             

            The discussions in there get me close.  I can reproduce what both Jonathan Drummey and Ross Bunker did in there.  The only problem is that it requires me to put the dimension I am COUNTD-ing onto the viz.  I can even make nice bar chart of it (attached).  But my viz is supposed to be bars by running donations, with the COUNTD of donors in tooltips.

             

            I have a half-a-hack recourse in that I can just supply an extra viz (such as Sheet6 in the attached).  But I would consider that a defeat.

            • 3. Re: Trying to get a COUNTD across a rolling span of months
              Jonathan Drummey

              Hi Joe,

               

              Here's the gigantic thread on running count distinct: How to count distinct users on a running period

               

              Fundamentally the problem is that you need to count each donor N times, where N is 13-(month donated). Table calcs can let us do that when the donor is in the view, other alternatives are to pad out the data (either in Tableau using densification or via a custom query), use a set of nested LOD calcs, or do the nesting yourself in a custom query.

               

              In this case I think I got the view you wanted:

               

              2015-08-05 09_15_55-Tableau - Running countd for donors jtd.png

               

              Here's the tooltip:

               

              2015-08-05 09_16_12-Tableau - Running countd for donors jtd.png

               

              You'll notice that the Constituent Definition Rollup Id is in the view. There are three table nested calcs (jtd 1,2,3) that count the IDs. The first identifies the first instance of each ID in each year, the second counts the IDs in the month (using the IF FIRST()==0 technique to only return one record to the partition), and then the third does a running sum. There are two more nested table calcs (jtd 4 + 5) that get the sum of donations per Month and then do a running sum on that. I haven't listed out all the addressing, you can get that by looking at the calcs.

               

              Note that the jtd 2. Count for Month calc is only counting *new* donors in that month for the year, not existing donors. This is necessary to get an accurate running CountD. If you want the count for donors in the month then you could build another calculation an IF FIRST()==0 THEN WINDOW_SUM(MIN(1)) END with a Compute Using on the ID.

               

              If you have any questions let me know!

               

              Jonathan

              • 4. Re: Trying to get a COUNTD across a rolling span of months
                Yuriy Fal

                Hi all,

                 

                Just looking around and connecting dots :-)

                Found recent thread on a similar topic here

                with the LOD calc (# 2) from Rody Zakovich

                 

                Re: Running Total with a Count Distinct

                 

                Easy to apply to this case, too.

                 

                Please find the attached copy of Jonathan's wb

                with a Running CountD : LOD calc added.

                 

                Yours,

                Yuri

                • 5. Re: Trying to get a COUNTD across a rolling span of months
                  Rody Zakovich

                  Yuryi, I like what you have done here

                   

                  RUNNING_SUM(COUNTD(IF [Fiscal Month] = 

                  { INCLUDE [Constituent Definition Rollup Id]:

                    MIN(

                         { EXCLUDE [Fiscal Month],[Fiscal Month String] : MIN([Fiscal Month]) }

                       )

                  }

                  THEN [Constituent Definition Rollup Id]

                  END))

                   

                  The inner most LoD

                   

                  { EXCLUDE [Fiscal Month],[Fiscal Month String] : MIN([Fiscal Month]) }


                  Inherently includes the Fiscal Year (Since it is within the vizLoD), so by Excluding Fiscal Month and Fiscal Month String,we limit limit the data set to return the MIN([Fiscal Month]) within each Fiscal Year.


                  By wrapping a second LoD which INCLUDES Constituent Definition Rollup Id (Which is not in the vizLoD), we force Tableau to calculate the MIN([Fiscal Month]) for each Constituent Definition Rollup Id within each Fiscal Year (As opposed to the MIN(Fiscal Month) within the entire dataset). This allows us to do the IF/ELSE comparison record level per Constituent Definition Rollup Id and Fiscal Year, and then COUNTD into the appropriate partitions. Of course we still have to force our RUNNING_SUM to Partition on Fiscal Year and Address Fiscal Month, Fiscal Month String.


                  The FIXED equivalent to this is


                  RUNNING_SUM(COUNTD(IF [Fiscal Month] = 

                  { FIXED [Constituent Definition Rollup Id], [Fiscal Year] : MIN([Fiscal Month])

                  }

                  THEN [Constituent Definition Rollup Id]

                  END))


                  Jonathan Drummey , since there are no filters in the viz, will the queries Tableau sends back to the datasource be the same for both calcs?


                  Regards,

                  Rody


                  • 6. Re: Trying to get a COUNTD across a rolling span of months
                    Jonathan Drummey

                    I don't know exactly, I'd suggest trying it out on a source where you can

                    view the queries and check.

                     

                    On Fri, Nov 6, 2015 at 8:48 AM, Rody Zakovich <rody.zakovich@patlive.com>

                    • 7. Re: Trying to get a COUNTD across a rolling span of months
                      Rody Zakovich

                      Hey Jonathan,

                       

                      Yeah I am going to check on this today.

                       

                      I am curious if Tableau is smart enough to recognize that the nested INCLUDE/EXCLUDE LoD is the same as the FIXED LoD (Since there are no filters in use).

                       

                      I'll update this thread with what I find.

                       

                      Regards,

                      Rody

                      • 8. Re: Trying to get a COUNTD across a rolling span of months
                        Rody Zakovich

                        Jonathan Drummey

                         

                        Ok, that was easier to test than I thought! And I am very happy with the results!

                         

                        For this particular example (Where there are no filters at play) Tableau issues the same query for

                         

                        COUNTD(IF [Fiscal Month] =

                        { INCLUDE [Constituent Definition Rollup Id]:

                          MIN(

                               { EXCLUDE [Fiscal Month],[Fiscal Month String] : MIN([Fiscal Month]) }

                             )

                        }

                        THEN [Constituent Definition Rollup Id]

                        END)

                         

                        AND

                         

                        COUNTD(IF [Fiscal Month] =

                        { FIXED [Constituent Definition Rollup Id], [Fiscal Year] : MIN([Fiscal Month])

                        }

                        THEN [Constituent Definition Rollup Id]

                        END)

                         

                        The nested INCLUDE/EXCLUDE calculation is sent back via ONE sub query, which makes sense. So when Tableau is constructing the Query for an LoD Calc, it is looking at the calculation in it's entirety, and constructing the most optimal query for it. That is why, in this particular case, the queries for both calculations are the same.

                         

                        This ties back to Should I use FIXED or INCLUDE/EXCLUDE?

                         

                        Whether of not we decide to use INCLUDE/EXCLUDE vs FIXED LoDs is entirely dependent of our situation and expected results. For situations like this, it seems writing the FIXED LoD would be the preferred method.

                         

                        Now, where it gets really interesting......

                         

                        We THINK, when we use Dimensional Filters, that INCLUDE/EXCLUDE is better because we don't have to add the Dimensional Filter to CONTEXT. When using INCLUDE/EXCLUDE the Dimensional Filter is included in the WHERE Clause for BOTH the Sub-Query and the Outer Query. FIXED LoDs (Without the Dimensional Filter in CONTEXT) only applies the Dimensional Filter to the WHERE Clause of the Outer Filter. And we don't want to use a Dimensional Filter in CONTEXT, because that can hurt performance.......

                         

                        Believe or not, when we add the Dimensional Filter to CONTEXT on the FIXED LoD, and we just add a regular dimensional Filter to the INCLUDE/EXCLUDE LoD, Tableau issues the SAME query to the DB for both.

                         

                        Looking at this, it makes complete sense. Both LoD calcs are creating a nested Sub-Query already. So Tableau doesn't need to create another Temp Table for CONTEXT, it simply just needs to add a where condition to the already developed Sub-Query (Which results in a mirror image of the nested INCLUDE/EXCLUDE LoD Query). So adding the Dimension to CONTEXT (In situations like this) would not affect performance (At least in relation to using a nested INCLUDE/EXCLUDE).

                         

                        I am going to work/test this some more. Also, I will be working on getting the Queries put together so that everyone can inspect the queries more in-depth.

                         

                        If I have miss-stepped, or said anything that may be incorrect, please let me know!

                         

                        Thanks,

                        Rody