6 Replies Latest reply on May 16, 2018 5:46 AM by Stephen Down

    SUMIF across multiple data sources

    Stephen Down

      I'm trying to create an absence report, and I've run up against a problem where I want to aggregate data from multiple data sources.

       

      In my source spreadsheet, I have two sheets:

      PupilList, which has a list of all children in the school, under the headings: Class | Name | Gender | Sessions

      where 'Sessions' is the number of sessions that each child should have attended ... but because children join and leave the school at different times, that isn't the same across the board.

      Absence, which has a list of each child's absences, under the headings: Class | Name | Absence | Reason

      where 'Absence' is the number of sessions that the child missed in that period of absence. Each child can appear several times in this table, if for example they have had 5 days off ill and then 5 days off on holiday then that would appear as two separate rows, one for each reason. Children who haven't been absent at all do not appear in this table.

       

      I have imported this into Tableau as:

      - Absence + PupilList (left join, matched on Class and Name fields)

      - PupilList

       

      The left-join is required to pull in the additional information like gender (I should add that this is a stripped-down example, and the real file has many more fields in).

      What I now want to do is to add a calculated field in PupilList that gives the percentage of sessions that each child has missed.

      In Excel, this would be easy:

      =SUMIFS(Absence!C:C,Absence!A:A,A2,Absence!B:B,B2)/D2

      but I would prefer to do it in Tableau than add it into the source data.

       

      I tried setting up the calculated field as

      SUM([Absence+ (20180510 Absence demo)].[Absence])/SUM([Sessions])

      but it's just giving null errors all the way down the line.

       

      How can I create this aggregate function within Tableau? Any suggestions would be gratefully received!

       

      (Tableau version 10.3)

        • 1. Re: SUMIF across multiple data sources
          Jim Dehner

          good morning

          see the attached

           

          the formula below

           

           

           

          will return this

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: SUMIF across multiple data sources
            Stephen Down

            Hi Jim,

             

            Thanks - apart from adding the ISNULL bit, it looks like I was on the right track ... but ... I was hoping that the calculated figures would be added to the Data Source tab so that I can then do more calculations with them. But it looks it might be tricky to aggregate data that is already aggregated :-(

             

            What I wanted to be able to do next is to create a flag for pupils with an absence rate of over 20%, along the lines of

            IF [Absence rate]>=0.2 THEN 1 ELSE 0 END

            so that I can report on how many children in each class, or how many boys/girls, have a high absence rate. But when I try to do anything like that, it gives me the flag for each child correctly, but if I take Name out and try to just look at it against Class or Gender then it just gives me zero for everything.

             

            Is there any way to do that in Tableau, or do I need to grit my teeth and do all that side of the number crunching in Excel before bringing it into Tableau?

             

            Sorry for not giving the full story in the first question ... I thought I would be able to work it out from there, but it turns out I was wrong!

            • 3. Re: SUMIF across multiple data sources
              Jim Dehner

              Stephen - it is bad form to not be upfront with the need - just wastes a lot of time

               

              see the attached

               

              it will return this

               

              or this for class

              or this for student

               

               

              Jim

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              1 of 1 people found this helpful
              • 4. Re: SUMIF across multiple data sources
                Stephen Down

                Hi Jim,

                 

                Thanks for the detailed reply - that looks like it's going to do just what I need it to. Fantastic!

                (Sorry for not replying sooner, I've been caught up on another project for the last few days)

                 

                I take your point about not being up front ... I was trying to be self-sufficient, only asking for the bare minimum help and doing as much of it myself as I could, and I thought I would be able to get it to work from there ... but it turned out I was wrong :-(

                 

                Stephen

                • 5. Re: SUMIF across multiple data sources
                  Jim Dehner

                  Thanks

                  Glad to help out

                  Jim