on 2020 Jul 06 5:42 PM
I have a crosstab with calculated members that creates an average. The problem is it always rounds down. So, if the average is 3.8, it will display as "3". I would prefer it to round to 1 decimal.
However, even when changing the formatting of the field to round to 1 decimal place, it just adds zeros (3.0).
I assume it may be related to my average formula that uses a crosstab value which is a "count".
Any advice how I could change this formula, to fix this rounding issue? Thanks!
local numbervar i; local numbervar avg; local numbervar cnt;
for i := 1 to CurrentColumnIndex-1 do ( avg := avg + GridValueAt(CurrentRowIndex, i, CurrentSummaryIndex); cnt := cnt + 1; ); (avg+GridValueAt(CurrentRowIndex, 0, CurrentSummaryIndex))/(cnt+1);
Hi Cheryl,
You'd have to use a "display string" formula.
1. Right-click the header cell for the "Avg" column > Calculated Member > Edit ColumnValue formula.
The commented text at the top would indicate the datatype needed for this column. If it says datetime then use the formula below:
datetime(1890,01,01,00,00,00)
If it says date then use:
date(1890,01,01)
If it says text then use:
"Avg"
2. Right-click one of summary values in the calculated column > calculated member > edit calculation formula > delete the code and type in 0
3. Right-click one of the summary cells > format field > common tab > click the formula button beside "display string" and use this code:
IF year(GridRowColumnValue("field used as crosstabs column", CurrentColumnIndex)) = 1890 then
(
local numbervar i;
local numbervar avg;
local numbervar cnt;
for i := 0 to CurrentColumnIndex-1 do
(
avg := avg + tonumber(GridValueAt(CurrentRowIndex, i, CurrentSummaryIndex));
cnt := cnt + 1;
);
totext(avg/cnt,1)
)
Else
totext(CurrentFieldValue,'#')
Note: Replace "field used as crosstabs column" with the correct database field. Also make sure that it is enclosed in double quotes and not curly braces.
-Abhilash
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
64 | |
10 | |
9 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.