6 Replies Latest reply on Jun 14, 2013 7:34 AM by Matt Lutton

    Question about Table Calc & Date Calculation

    Matt Lutton

      Hello all:

       

      I had a use case for a Table calc this week and thought I'd post this.

       

      I have been working on calculating an ACCURATE attendance rate, at the student level. I was able to get close by using total(countd([date])) which seems to return every possible date for a given time period (say 2012-13 school year).   Of course, I am pretty new to these concepts so I could be way off even at that.

       

      However, I have two issues I'd like some advice on, if possible:

      1) This calc is based on the idea that attendance is entered every school day.  For my case, that is probably true--but if anyone has a suggestion on how to deal with this issue (I'd like to know HOW, even if I can get by without it)

      2) More importantly, attendance is sometimes entered in advance--so a future date can be included   Can I remove future dates using a Date calculation of some kind?  I'm sure that I can, but I don't have a lot of experience with date calcs.

       

      I am beginning to understand the concepts, and really appreciate all the help I've received from experienced forum members thus far.  Thanks!

        • 1. Re: Question about Table Calc & Date Calculation
          Jim Wahl

          Hi Matt,

           

          Tableau will fill in missing dates between a start and an end date, even if there are no values for those dates in the view. Something called domain padding. You can control this with the show / hide missing values option on the date pill (blue in this case for discrete dates). But I don't think it's going to affect a COUNTD calculation. Or that you'd want it to, since datediff could be used to provide this value.

           

          Regarding your second question, you could add a date filter where Date <= TODAY().

           

          Also, you might be interested in Jonathan Drumey's blog post, where he describes calculating fall rates over a given period. Seems like a similar problem to yours.

           

          Tableau Data Blending, Sparse Data, Multiple Levels of Granularity, and Improvements in Version 8 | Drawing with Numbers

           

          Jim

          1 of 1 people found this helpful
          • 2. Re: Question about Table Calc & Date Calculation
            Matt Lutton

            Thanks Jim Wahl, for responding.

             

            I am not sure if show/hide will help much in this case, at least not without a lot of work (and limited by my current knowledge)--each school has a different calendar, and it would get pretty messy.  Would be much simpler if I just had a value for days possible, as well as a value of number of total students by class period, etc.  There are a lot of holes and issues with the data.

             


            Thanks for the filter idea, that is the type of thing I just overlooked in my initial thought process.  However, since I am using the total() calc to get number of days of attendance, I'm not sure if filtering will change the results.

             

            As always, thanks for your help.

            • 3. Re: Question about Table Calc & Date Calculation
              Jonathan Drummey

              When using TOTAL(COUNTD([date])), turning on domain padding will not change the result. This is because the TOTAL(COUNTD([date])) is issued as a separate computation to the data source, outside of the domain padding that is done. Also note that aggregates and row-level calcs will return no data (i.e. no value, not even Null) for padded dates because those calcs are performed in the data source, prior to domain padding.

               

              So, a DATEDIFF('day',[date],TODAY()) on a padded date will not return a value, because Tableau will also compute the DATEDIFF in the data source and the padded date doesn't exist there. In addition, Tableau does not give us access to the padded values in calculated fields. So if I want to use a padded date in a calculated field I'll use a table calc like DATEADD('day',INDEX()-1,TOTAL(MIN([date]))) to give me a date value I can use inside a DATEDIFF or another calc. It's definitely weird because we can see those padded dates just fine in the view, on tooltips, etc. but have to jump through hoops to use them in calculated fields.

               

              A row-level date filter like [Date]<= TODAY() is also computed in the query to the data source (it would be part of the WHERE clause in SQL), so it's done well before the table calc. So if you want to filter and affect results, do your filter as a row level or aggregate filter, if you want to filter without affecting results (like get rid of the empty results for the first month for a difference from prior calc) then you can use a table calc filter which is applied after the computations are complete, or use a manual hide, which is applied after that.

               

              Matt, I'm not sure what your question is about your first issue, can you clarify?

               

              Jonathan

              1 of 1 people found this helpful
              • 4. Re: Question about Table Calc & Date Calculation
                Matt Lutton

                Basically, using the total() of distinct dates assumes that a date is entered for every school day.  What if that wasn't the case?  Wouldn't the ideal scenario be that the possible number of days, based on the school calendar, be stored in the data source?  I do not believe that data is there now. 

                • 5. Re: Question about Table Calc & Date Calculation
                  Jonathan Drummey

                  Given what I know of the variation in school calendars (multiple school teachers in the family) I'd suggest having those calendars available so you have an authoritative source of school days to total up. There are a lot of ways to do this, one example is to use a data blend, here's an example for a 4-4-5 fiscal calendar that could possibly be adapted: http://community.tableau.com/docs/DOC-1450.

                  1 of 1 people found this helpful
                  • 6. Re: Question about Table Calc & Date Calculation
                    Matt Lutton

                    Yes, I agree.  Thanks for the help.