topic finding Avg, Min and Max in QlikView

finding Avg, Min and Max

Sun, 13 Apr 2014 07:29:47 GMT

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

Re: finding Avg, Min and Max

stevedark — Sun, 13 Apr 2014 09:45:22 GMT

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

Re: finding Avg, Min and Max

Sun, 13 Apr 2014 14:40:57 GMT

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')