14 Replies Latest reply on Feb 22, 2016 9:19 AM by fei.yang.1

    how to perform this countd calculation?

    fei.yang.1

      workbook attached. Using Tableau 9.2

       

      Original table every row includes following student absence information: student_id, absence_date, year, grade, school, district.

      The number_of_absences is a calculation that is a count(absence_date) from Custom SQL. In the workbook "5_or_more_absences" you can see that we can select each individual district, school and year, to see detailed information of which student had how many absences that year.

       

      Now we want to add some information such as in a particular district, school, and year, how many students had 10 more absences, and how many students had 5-10 absences. Something I tried to add a calculation as:

      IF ([number_of_absences] > 10) Then Countd ([misstudentid]) else 0 end------------This gave mixed aggregate error msg (however my 2nd question will be in my another packaged workbook which is too large to upload, that I didnot use custom SQL but just used join and filter in tableau, this calculation didnot give error message but just wrong result);

      I tried to only us "Countd ([misstudentid])"-------this has no error but gave wrong number as result

      I also tried to following previous discussions and tried "countd (IIF...)" but always gave boolean error msg.

       

      Any suggestions? Thanks!

        • 1. Re: how to perform this countd calculation?
          Joe Oppelt

          Sorry.  I deleted my previous reply by mistake.

           

          Attached shows two ways to do what you want.  Both tally 17 in this example.

           

          I modified your [10MORE] calc to pick out all rows that have 10 or more in [number of absences].  But that won't take into account the possibility of multiple rows for a give ID having 10 or more.  If you don't have dup ID records, then this will suffice.  That one give you a 1 or null based on [number of absences].  SUM it up and you have your count.


          But if you can have dup IDs, then that's why you would want to have a COUNTD.  I created a second calc that does a countd based on the [10more] calc.  In this case, you don't have dups.

           

          See attached.

          1 of 1 people found this helpful
          • 2. Re: how to perform this countd calculation?
            pooja.gandhi

            You can create a calc like:

             

            {fixed [YEAR], [DISTRICT_NAME], [MSISSTUDENTID], [SCHOOL_NAME]: IF sum([NUMBER_OF_ABSENCES]) >= 5 and sum([NUMBER_OF_ABSENCES]) < 10 then '5-10'

            elseif sum([NUMBER_OF_ABSENCES]) >= 10 then '10 or more' end }

             

            Place this on rows and COUNT(MSISTUDENTID) on text on marks card:

             

            2 of 2 people found this helpful
            • 3. Re: how to perform this countd calculation?
              Simon Runc

              hi FY,

               

              Try this formula...

               

              COUNTD (IIF([NUMBER_OF_ABSENCES]>10,[MSISSTUDENTID],NULL))

               

              By way of explanation please see my reply and follow up (with Excel explanation) here.

               

              How to create a Scatter Plot based on same calculation but different dimensions

               

              In short we are doing the Row Level calculation in the IIF part, and then Aggregating the result with the COUNTD bit. We equate the false value to NULL so it won't get counted by the COUNTD...I got an answer of 12!

               

              Let me know if this doesn't do the trick

              1 of 1 people found this helpful
              • 4. Re: how to perform this countd calculation?
                Joe Oppelt

                Hey!  I got 17!!

                 

                Then again, I did Greater-or-equal 10, and you did greater than 10.

                • 5. Re: how to perform this countd calculation?
                  fei.yang.1

                  Hi Simon,

                   

                  Thank you very much! For the workbook that I attached, your formula works great.

                   

                  However, I do have another question, I have another workbook which is too large to upload (1.4G), but basically they are connecting to the same data on our oracle database, the only difference is the the attached one I used custome SQL to join and filter, however for the one that I didnot attach, I used all the tableau to drag join and filter thus the number_of_absence is a calculation filed that I created from "count(absence_date)". Both of the workbooks gave me the same bar diagram of "5 or more absences" as the one showing in my attached workbook. However in the unattached one if I type in the same formula in calculation, it says "cannot mix aggregate and non-aggregate arguments with this function", I am sure it has something to do that the "number_of_absence" is calculated. How to fix that? Thanks.

                  • 6. Re: how to perform this countd calculation?
                    Simon Runc

                    Hi Joe,

                     

                    Yes I did >10!!...Just did >= and got 17...phew! (good to know all copies of Tableau count the same!!)

                    • 7. Re: how to perform this countd calculation?
                      fei.yang.1

                      Thank you very much! This also works!

                      • 8. Re: how to perform this countd calculation?
                        Joe Oppelt

                        Wrap your non-aggregate field in the ATTR() function.  Tableau will treat the non-aggregate as an aggregate and be happy.

                         

                        For instance, if you want to see the percentage of total for each row:

                         

                        [units] / [aggregate calc field]

                         

                        ... will give you the error for mixing non-aggregates with aggregates.

                         

                        If you do:

                         

                        ATTR([units] / [aggregate calc field]

                         

                        ... tableau will be happy, and will calc that for each row.

                        • 9. Re: how to perform this countd calculation?
                          Simon Runc

                          So now your [number_of_absence] is calculated, and calculated as an aggregate, you can no longer use it in a Row Level calculation...hopefully my explanation in the post I referenced explains why (let me know if not and I can go into a bit more detail).

                           

                          There are few ways round this, but probably the simplest is to use a LoD. This lets you create an aggregated field (such as COUNT) and then return that result at row level at the level specified.

                           

                          So for example. To re-create you calculation and return it against every [MSISSTUDENTID]

                           

                          first we create the LoD, and call it [Number of Missed Days by Student ID]

                           

                          {FIXED [MSISSTUDENTID]: COUNT(absence_date)}

                           

                          Now you can use this in our calculation

                          COUNTD (IIF([Number of Missed Days by Student ID]>10,[MSISSTUDENTID],NULL))

                           

                          If you wanted this count to by by Year and Student you would amend the LoD to

                          {FIXED [MSISSTUDENTID], [YEAR]: COUNT(absence_date)}

                           

                          As you are specifying the Level the calculation is run, the result of the LoD is independent of your VizLoD....which is why if you want it by year you need to specify this in the LoD.

                           

                          Hopefully that does the trick...and more importantly, makes sense!!

                          • 10. Re: how to perform this countd calculation?
                            fei.yang.1

                            Hi Joe,

                            When I try to add the calculation to my unattached workbook, in your "10more 2" formula, if i changed it to:

                            countd(if not isnull([10MORE]) then attr([MSISSTUDENTID]) end)

                            The the error is on COUNTD: argument to counted(aggregated function) is already an aggregation, and cannot be furthur aggregated.

                            • 11. Re: how to perform this countd calculation?
                              fei.yang.1

                              Hi Simon,

                               

                              Thanks for your input,

                              I did follow:

                              1. create the LoD, and call it [Number of Missed Days by Student ID]

                              {FIXED [MSISSTUDENTID]: COUNT(absence_date)}

                              2. created this 10more calculation: COUNTD (IIF([Number of Missed Days by Student ID]>10,[MSISSTUDENTID],NULL))

                              -------------This gave a result of 100+ even though I filtered year and district name in filter

                               

                              As you suggested if I change the 1st step to:{FIXED [MSISSTUDENTID], [YEAR]: COUNT(absence_date)}-------------------This gave a result of 14.

                              So I furthur changed the 1st step to :{FIXED [MSISSTUDENTID], [YEAR], [DISTRICT NAME]: COUNT(absence_date)}-------------------This gave a result of 12.

                               

                              Thus: we must add the condition in LoD regardless of filtering selection in the drop down menu? Thanks.

                              • 12. Re: how to perform this countd calculation?
                                Joe Oppelt

                                Right.  You can only COUNTD on a row-level field. 

                                 

                                Do the COUNTD when you do the initial aggregation.

                                 

                                One caveat:  are you blending two data sources when you do this?  You won't be able to do a COUNTD of values in a secondary source that is blended.

                                • 13. Re: how to perform this countd calculation?
                                  Simon Runc

                                  Excellent...Yes as we are FIXING the level of aggregation, you need to add every level you want to the Left hand part of the FIXED LoD...and is independent of the VizLoD. It is independent of filtering too, as you say, unless the filter is made 'in context'. This pushes it up the 'Tableau Order of Filtering Operations' (Evolution of the Order of Operations Diagram ) and now the LoD will reflect the context filter.

                                   

                                  I should also add that making a filter in-context and using FIXED LoDs have a performance cost (as they add extra queries), but sounds like that isn't an issue...if it is, the best thing (I've found anyway) would be to 'hard code' the LoD as a materialised field in the SQL prior to bringing into Tableau. The LoD (looking at your example) is creating a temp-table, at the level [MSISSTUDENTID]/[YEAR]/[DISTRICT NAME] with the COUNT([absence_date]) as the aggregated measure, and then rejoining this to the original data (joined on [MSISSTUDENTID]/[YEAR]/[DISTRICT NAME])...so you could just do the same in the view creation.

                                  • 14. Re: how to perform this countd calculation?
                                    fei.yang.1

                                    Joe, Pooja and Simon,

                                     

                                    Thank you for all  your wonderful inputs. I think after comparing the two ways to create workbooks dashboard, obviously to use the custom SQL at beginning to join, filter, and calculate fields before even starting any workbook is a better method in my case.