Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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')