12 Replies Latest reply on Jun 6, 2013 5:17 PM by Dimitri.B

    Need help with Calculation

    Matt Lutton

      I am working with attendance data, and when I try to use a sum of unexcused absences I get a total that includes duplicates (like a student who is marked absent for two periods).  Instead, I'd like to only count that student once, so I can end up with a day count absence trend graph.

       

      Can I adjust my calculation in order to only count students who are absent in multiple periods on the same date once?  There is a field called "ClassPeriodName" that tell me what period the student was marked absent for. [Absence] tells me whether its a tardy or absence and [AbsenseType] (yes, it was misspelled by the previous person) tells me if its excused, unexcused, etc.

       

      Please help if you can!

        • 1. Re: Need help with Calculation
          Dimitri.B

          Matt, the inserted image is too small to see detail. Can you resize the workbook to something smaller and re-attach, or just copy-paste the calculation formula?

          You might need a table calculation for this kind of task.

          1 of 1 people found this helpful
          • 2. Re: Need help with Calculation
            Matt Lutton

            When I click on the image, it provides a full screen view.  Sorry if its difficult to see, I'm working to find a way to scramble my data so I can provide packaged workbooks... in the meantime, please vote for my idea if you feel it would help!  http://community.tableau.com/ideas/2238

             

            The current calculation is as such:

             

            If[Absence] = "Absent" AND [AbsenceType]="Unexcused" then 1 else 0. 

             

            The problem is that absences are presented as any occurence/period a student is marked absent.  So, the same student could be marked absent three times in one day, and therefore, three unexcused absences would be counted.  I'm attempting to create a day count.

             

            I'm hopeful this type of thing can be sorted by the data warehouse folks, but I should probably learn as much as I can about handling it on the Tableau side as well.  Thanks for taking the time to look at this.

            1 of 1 people found this helpful
            • 3. Re: Need help with Calculation
              Dimitri.B

              There might be a way to do it via table calculations, but I'd need sample data to try that. I can do it on a completely different set of data to show how, but then it might not work for you.

              1 of 1 people found this helpful
              • 4. Re: Need help with Calculation
                Matt Lutton

                Maybe, but I've seen and studied some table calculations and am not sure it applies here (but could be wrong, of course).  Basically, I think I need a way to calculate:

                 

                -IF a student is marked absent more than once for the same date, only count it once.

                 

                It may make more sense to do this on the data warehouse side.  It would sure be easier to work with the data if it was in the format I need!

                • 5. Re: Re: Need help with Calculation
                  Matt Lutton

                  Dimitri Blyumin:

                   

                  Attached is a sample.  This shows 700+ for a given day, because absences are counted by each period, and not on a day count.  I took out the individual schools so this is a total of several schools, but is still much too high due to counting every period absence instead of a day count.  I realize this would be much easier to handle if the data was presented to me cleanly, but I have to work with what I have in the mean time.

                  1 of 1 people found this helpful
                  • 6. Re: Re: Re: Need help with Calculation
                    Dimitri.B

                    You don't need a table calc here, just a slight modification of the existing calculation (see attached).

                    1 of 1 people found this helpful
                    • 7. Re: Need help with Calculation
                      Matt Lutton

                      Thanks, Dimitri Blyumin, am checking the work now.  I see your breakdown by absence date, which was helpful in seeing the correction.  However, I would need a total for each student, based on a filtered time period (shown in the sample data).  I cannot sum the already aggregated data.  Is there a work around?  Is this a use for a Table Calculation, Jonathan Drummey or any of you other helpful folks out there?

                       

                      Appreciate your help!  The forum has been extremely helpful for me, especially in terms of understanding the work done by others.

                      • 8. Re: Need help with Calculation
                        Dimitri.B

                        There is a way to work around this issue as well and count all absences in one day as one. So even if you roll up days into weeks or months, the correct count will be shown.

                         

                        I can take a look at it tomorrow (no Tableau today).

                        1 of 1 people found this helpful
                        • 9. Re: Need help with Calculation
                          Matt Lutton

                          I've no doubt there is a way.  Using a running total table calculation, I can see the numbers I want and I imagine there's a way to return the MAX of the running total, but I am not sure if that is the right approach and I did not get to it today.  If anyone else wants to chime in, please do!  I imagine I will encounter this scenario often.

                           

                          As always, thanks to everyone who is contributing to my education here.

                          • 10. Re: Re: Need help with Calculation
                            Dimitri.B

                            Attached is the improved version, which calculates absences per student per day, counting multiple absences in one day as one, and rolls up correctly into weeks, months, etc.

                            • 11. Re: Re: Need help with Calculation
                              Matt Lutton

                              Excellent, thanks Dimitri! I really appreciate this, and seeing other people's work in scenarios I am working with has been most helpful.

                              • 12. Re: Need help with Calculation
                                Dimitri.B

                                Matthew asked how it works, so here it is:

                                 

                                'Days logged' is a count of days for which records exist in data, regardless of attendance. Not all calendar days might be present, so we need to calculate this field to be able to show % of days with absences. As there are records for different students on the same date, to avoid duplicate count we need to count distinct days. Tableau's 'Date' data type is a complex thing with various levels built-in, i.e. years, quarters, week numbers, etc., so we convert it to string representing a date, i.e. STR([Some Date]) produces '06-Jun-2013' or similar, which is just what we need. Then we do COUNTD() of those, which gives us count of distinct days in data.

                                 

                                For absences we use similar approach. As each student can be absent from multiple periods on the same day, we need to count distinct student IDs within each day. The simple hack it is to combine student ID and date string from above into one string, which will roll up multiple absences of one student on one day into a single unique tag. Then we do COUNTD() on those tags and get a single count for each student-day with any absences. The IIF clause counts only those records that qualify as absence.

                                 

                                NOTE: The 'Days logged' calculation might need to be modified further, depending on how it is used, e.g. if we want a bigger picture and are not using student ID on the sheet, then we'll need to add Student ID to the date string, just like we did for absences, but without the IIF.

                                1 of 1 people found this helpful