topic Re: Double of average in QlikView

Double of average

didierodayo — Wed, 20 Jul 2016 08:17:47 GMT

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

Re: Double of average

hic — Wed, 20 Jul 2016 08:27:24 GMT

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

Re: Double of average

didierodayo — Wed, 20 Jul 2016 08:55:40 GMT

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

Re: Double of average

hic — Wed, 20 Jul 2016 09:18:29 GMT

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

Re: Double of average

didierodayo — Wed, 20 Jul 2016 09:32:19 GMT

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

Re: Double of average

hic — Wed, 20 Jul 2016 09:43:51 GMT

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

Re: Double of average

didierodayo — Wed, 20 Jul 2016 09:54:07 GMT

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.

Re: Double of average

hic — Wed, 20 Jul 2016 10:45:56 GMT

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

Re: Double of average

didierodayo — Wed, 20 Jul 2016 11:00:45 GMT

Thanks a lot Henric ,

That's exactly what I am trying to achieve.

Didier