finding Avg, Min and Max

[#1]

Hi Have sets of data with MODEL, USER_ID, AVG_ALL, AVG_ONLINE, AVG_OFFLINE

I am creating a statistics box which displays two sets of data

First Set is derived from the total

Total User Count: =Count(DISTINCT USER_ID)

SUM:=SUM({1} TOTAL AVG_ALL)

Min:=Min({1} TOTAL AVG_ALL)

Max:=Min({1} TOTAL AVG_ALL)

Now I want to find the above for users who has AVG_ALL > 0

Is the following correct?

Regular Users Count: =count(distinct if(AVG_ALL >0, USER_ID))

Sum: =sum( if (AVG_ALL >0, AVG_ALL))

Min: =Min( if (AVG_ALL >0, AVG_ALL))

Max: =Max( if (AVG_ALL >0, AVG_ALL))

One of the issue I noticed is I want to know the Min value for users of AVG_ALL>0. I am getting always 0.

Any other way we can write using set analysis or AGGR function?

Here is my Text Box Equation

='T.Count       :' & Num($(vUsersCount),'#,##0') & chr(13) &

'T.Sum          :' & Num(sum({1}AVG_ALL),'#,##0') & chr(13) &

'T.Average    :' &Num( Avg({1}AVG_ALL),'#,##0') & chr(13) & 'T.Median      :' & Num(Median({1}AVG_ALL),'#,##0') & chr(13) &

'T.Min            :' & Num(Min({1} AVG_ALL),'#,##0') &chr(13) & 'T.Max           :' & Num(Max({1} AVG_ALL),'#,##0') &chr(13) &

'--------------------------------' & chr(13) &

'N                   :' & Num(Count (DISTINCT if(AVG_ALL >0, USER_ID)),'#,##0') & chr(13) & 'N%                :' & Num(Count (DISTINCT if(AVG_ALL>0, USER_ID))/$(vUsersCount),'#.00%') & chr(13) &

'Average         :' &Num( Avg(if(AVG_ALL>0, AVG_ALL) ),'#,##0') & chr(13) & 'Median           :' & Num(Median(if(AVG_ALL>0, AVG_ALL)),'#,##0') & chr(13) &

'Min                 :' & Num(Min(if(AVG_ALL>0, AVG_ALL)),'#,##0') & chr(13) &  'Max                :' & Num(Max(if(AVG_ALL>0, AVG_ALL)),'#,##0') &chr(13) &

'Sum               :' & Num(sum(if(AVG_ALL>0, AVG_ALL)),'#,##0') & chr(13)

Thanks

[stevedark]

Hi Ram,

I would expect that you are seeing zero for =Min( if (AVG_ALL >0, AVG_ALL)) as you have a row in your set with a value that is very close to zero (eg. 0.0001) - or there are no values greater than zero.

I would suggest adding a List Box showing all values of AVG_ALL to see what you have in there.

You could rewrite the expression to be:

=Min( if (AVG_ALL >=1, AVG_ALL))

To only give values that are 1 or above.

Or show the expression to a large enough number of decimal places:

=Num(Min( if (AVG_ALL >=1, AVG_ALL)), '#,##0.000000')

From the name of your field it would appear that you are pre-calculating averages when loading the data?  If so, you may be getting confusing results as the pre-calculated averages would not be changing based on selections.  I appreciate this may be the desired way of working though.

Hope that helps,

Steve

[#3]

Hi Steve

Thank you for your inputs. I have data in between 0 to 1. So Should I rewrite the equation

=Num(Min( if (AVG_ALL >=0.001, AVG_ALL)), '#,##0.000000')