Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Use below Expression.
Count(id)-(20*Count(id))/100
Now u can get the 80% from Whole Count.
Regards,
Kabilan K.
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 |
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.
PFA.
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%)