5 Replies Latest reply: Jul 30, 2013 9:39 AM by Alessandro Bettica

# top percent in qlikview

Hi guys,

i've a big problem and i don't know how to solve it.

I'll try to describe the problem :

I've a database with a tree milions of record and i need to create a pivot table group by a data and apply a count of records(it's seem to be easy till now....)

the count should be only on 80% of records for each data showed on pivot table(something like top percent in sql but grouped by data), i.e. :

data               id

01/01/2013     100

01/01/2013     101

01/01/2013     102

01/01/2013     103

02/01/2013     104

02/01/2013     105

02/01/2013     106

03/01/2013     107

03/01/2013     108

03/01/2013     109

03/01/2013     110

03/01/2013     111

03/01/2013     112

03/01/2013     113

03/01/2013     114

03/01/2013     115

03/01/2013     116

the pivot should be :

01/01/2013     3

02/01/2013     2

03/01/2013     8

I hope it's clear.

pls help me.

thx a lot.

bye.

• ###### Re: top percent in qlikview

Use below Expression.

Count(id)-(20*Count(id))/100

Now u can get the 80% from Whole Count.

Regards,

Kabilan K.

• ###### Re: top percent in qlikview

see attachement

and  in expression write this syntax

round(count(id)-count(id)*0.2)

then output like this

 data round(count(id)-count(id)*0.2) 01/01/2013 3 02/01/2013 2 03/01/2013 8
• ###### Re: top percent in qlikview

ok, thx a lot, but my project is more complex(i know i didn't said in the first example), see attached file please.

here it's column ta_1 too.

this column is a pre-calculated time on db, now i need to do the avg of this value only of 80% of records grouped by data in my pivot table.

thx again.

• ###### Re: top percent in qlikview

PFA.

• ###### Re: top percent in qlikview

It's no good unfortunatly.....

try to make a filter on data 03/01/2013 you'll have these results :

count  = 8

avg = 15.7    (this is the result of sum of ten ta_1 values divided by count of id)

but.....should be

count = 8

avg = 19.25 (this is the result of avg of first 8 record - 80% of 10 - divided by count of first 8 record  )

i can't do the avg of first 8 record (80%)