5 Replies Latest reply on Aug 8, 2018 7:27 AM by Zhouyi Zhang

    Calculate Per-Column Statistics Dynamically

    John LaPlante

      Hi all!  I'm hoping someone has some experience with this:

       

      I am reporting on the number of discipline referrals in our school buildings.  Here's what I put together:

       

       

      The "Referral Date" is on the columns shelf, and the level is set to "Month".  The Distinct Count of discipline referrals is on the rows shelf.  I thought this was good...but...

       

      Now they want the number of referrals per school day per month.  The number of school days is variable due to scheduled non-school days.  So I'm thinking of a calc like this:

       

      COUNTD([Incident Number]) / **if (month of column) = "September" then 21, else if (month of column) = "October" then 23....etc.

       

      So in the above calc, the 517 for September would turn into 517/21 or 24.61, and 1364 in October would turn into 1364/23 or 59.3.

       

      I hope this makes sense...thank you all!

       

      John