Double of average

[didierodayo]

Hello,

I would like to return the count of a field (system) IF the count of the field is double or more the average.

e.g.

LOGID	SYSTEM_TYPE	USER	ACCESSTIME

My dimension is user and the count of system_type is returning a big chart because it is a big table. So I am only interested in system_types accessed more than twice the average time of for the user. I want the result on a bar chart that displays the Users and the count of the system_type that was accessed double the average time.

I welcome any idea.

Thank you

[hic]

So, for each combination of user and system type, you want to calculate the average access time.

Then you want to compare this with the average access time for that user irrespective of which system type it is.

And then you want to count the system types for which this ratio is 2 or greater. Is this correct?

If so, I think you should use

   Count(distinct

      Aggr(

         If(Avg(ACCESSTIME) / Avg(total <USER> ACCESSTIME)>=2,SYSTEM_TYPE),

         USER,

         SYSTEM_TYPE

         )

      )

HIC

[didierodayo]

Hi Henric,

sorry the accesstime is misleading, it is actually a Timestamp field. and the System_Type is what am counting.

I want to count how many total( SYSTEM_TYPE) were access per user . BUT I would like to know this only WHERE the access ratio is 2 or greater.

Thanks

[hic]

The expression does indeed count the number of system types. But now I don't understand your criterion: The ratio should be a factor 2 or greater, that's understood. But, the ratio of what?

HIC

[didierodayo]

Hi Henric,

The ratio of the count(SYSTEM_TYPE) FOR every user.

I think my example was confusing. the user here is not the one accessing the systemtypes.

The systemtypes are accessed to see the user records. so I have counted the systemtypes for dimension user and from the result I would now like to count only where the total count per user is more than double the average for all users.

I hope I managed to explain properly. Please let me know.

Thanks

Didier

[hic]

But the count(SYSTEM_TYPE) for a specific user and system type can never be greater than the overall count for the user. So the requirement that it should be 2 or greater doesn't make sense.

I think you need to post an example.

HIC

[didierodayo]

Hi Henric,

It is going to count(SYSTEM_TYPE) for all the user and the average count of that is what is going to be the basis to define the users who had a Total count(SYSTEM_TYPE) more than double the average.

so let say after the count for all user the average is 25 then Chart I want to create should on show the count for user where (SYSTEM_TYPE) is >=50.

[hic · Accepted solution]

First, I suspect you want to use Count(distinct ...) instead of a standard count.

As I understand it, you want to calculate

  Count(distinct SYSTEM_TYPE) for one specific user

and compare that with

  the Average over all users of Count(distinct SYSTEM_TYPE).

If so, you should try the following as ratio

  Count(distinct SYSTEM_TYPE) / Avg(Aggr(Count(distinct SYSTEM_TYPE),USER))

and the following as expression in a chart with USER as dimension

  If(

    Count(distinct SYSTEM_TYPE) /

      Avg(Aggr(Count(distinct SYSTEM_TYPE),USER))>=2,

    Count(distinct SYSTEM_TYPE)

  )

HIC

[didierodayo]

Thanks a lot Henric ,

That's exactly what I am trying to achieve.

Didier