Hello,
Each week I run a query of the database our company uses to generate counts of certain events that is tracked by our leadership team. I then take those counts and convert them to rates using a separate excel worksheet. Lately, I have tried to calculate the rates via the pivot table I create to get the counts to no avail. The pivot table looks like this:
Type of Event SUM of TypeofEvent
Nav 4
TCAS 6
BTB 10
Mx 3
I have tried using the calculated field function to bring rates into the pivot table as well. The formula we use to calculate our rate is number of events (info above)/total number of transactions (this number varies each week-I would enter this "manually" into the formula (ie-it would not be contained in a column) multiplied by a normalizer (in this case 10000-also manually entered into the formula). After using the calculated field function the result looks like:
Type of Event SUM of TypeofEvent Sum of Rate
Nav 4 0.00% TCAS 6 0.00%
BTB 10 0.00%
Mx 3 0.00%
There is no grouping applied to the dates used to determine the type of events count but they are date filtered (ie-I select the applicable week period). I tried clearing the date filter and still received the same result (all zeros in rate)
The formula entered into the calculated field looks like =TypeofEvent/3300*10000
Note-3300 is number of transactions for week
Does anyone have any idea what I am doing wrong (or if it is possible to perform this type of calculation in a pivot table)? I greatly appreciate any help/advice anyone may have on this.
Thank You,
Skip
Each week I run a query of the database our company uses to generate counts of certain events that is tracked by our leadership team. I then take those counts and convert them to rates using a separate excel worksheet. Lately, I have tried to calculate the rates via the pivot table I create to get the counts to no avail. The pivot table looks like this:
Type of Event SUM of TypeofEvent
Nav 4
TCAS 6
BTB 10
Mx 3
I have tried using the calculated field function to bring rates into the pivot table as well. The formula we use to calculate our rate is number of events (info above)/total number of transactions (this number varies each week-I would enter this "manually" into the formula (ie-it would not be contained in a column) multiplied by a normalizer (in this case 10000-also manually entered into the formula). After using the calculated field function the result looks like:
Type of Event SUM of TypeofEvent Sum of Rate
Nav 4 0.00% TCAS 6 0.00%
BTB 10 0.00%
Mx 3 0.00%
There is no grouping applied to the dates used to determine the type of events count but they are date filtered (ie-I select the applicable week period). I tried clearing the date filter and still received the same result (all zeros in rate)
The formula entered into the calculated field looks like =TypeofEvent/3300*10000
Note-3300 is number of transactions for week
Does anyone have any idea what I am doing wrong (or if it is possible to perform this type of calculation in a pivot table)? I greatly appreciate any help/advice anyone may have on this.
Thank You,
Skip
