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

# Calculate Per-Column Statistics Dynamically

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

• ###### 1. Re: Calculate Per-Column Statistics Dynamically

Hi, John

countd([Incident Number])/ avg(if month = 'September' then 21 elseif month = 'October' then 23 elseif ..... end)

ZZ

• ###### 2. Re: Calculate Per-Column Statistics Dynamically

Thanks!

Just for everyone else, here is the code I ended up with:

COUNTD([Incident Number]) / avg(if MONTH([Incident Date]) = 8 then 14

ELSEIF MONTH([Incident Date]) = 9 then 19

ELSEIF MONTH([Incident Date]) = 10 then 21

ELSEIF MONTH([Incident Date]) = 11 then 19

ELSEIF MONTH([Incident Date]) = 12 then 16

ELSEIF MONTH([Incident Date]) = 1 then 20

ELSEIF MONTH([Incident Date]) = 2 then 18

ELSEIF MONTH([Incident Date]) = 3 then 17

ELSEIF MONTH([Incident Date]) = 4 then 20

ELSEIF MONTH([Incident Date]) = 5 then 22

ELSE 30

END)

• ###### 3. Re: Calculate Per-Column Statistics Dynamically

Hi, John

ZZ

• ###### 4. Re: Calculate Per-Column Statistics Dynamically

Whoops!  Marked the wrong one as correct...proper credit given.  Thanks so much!

• ###### 5. Re: Calculate Per-Column Statistics Dynamically

Thank you!

ZZ