topic Re: count is not correct in QlikView

count is not correct

arulsettu — Thu, 01 Oct 2015 10:29:43 GMT

hi i am trying to count the policy so did like this

count(distinct employee_id).

and dimension is age band

like this

if(ME_AGE>=0 and ME_AGE<=6,'0-6',

if(ME_AGE>=7 and ME_AGE<=18,'7-18',

if(ME_AGE>=19 and ME_AGE<=30,'19-30',

if(ME_AGE>=31 and ME_AGE<=45,'31-45',

if(ME_AGE>=46 and ME_AGE<=60,'46-60',

if(ME_AGE>=60,'60')))))) as age_band

but the result getting is not correct. while checking in db output is little differs in qlikview.

here i attached a sample app please check and let me know what i am doing wrong.

Re: count is not correct

nagarjuna_kotha — Thu, 01 Oct 2015 10:41:54 GMT

Which count you are saying about

Re: count is not correct

arulsettu — Thu, 01 Oct 2015 10:42:41 GMT

count(distinct employee_id).

Re: count is not correct

ChennaiahNallani — Thu, 01 Oct 2015 10:43:41 GMT

Hi,

Use this

if(ME_AGE>=0 and ME_AGE<=6,'0-6',

if(ME_AGE>=7 and ME_AGE<=18,'7-18',

if(ME_AGE>=19 and ME_AGE<=30,'19-30',

if(ME_AGE>=31 and ME_AGE<=45,'31-45',

if(ME_AGE>=46 and ME_AGE<=60,'46-60',

'60'))))) as age_band

Re: count is not correct

arulsettu — Thu, 01 Oct 2015 10:46:35 GMT

expected output is

14594	0 to 6
15088	7 to 18
61451	19 to 30
74968	31 to 45
24283	46 to 60
2423	above 60

Re: count is not correct

nagarjuna_kotha — Thu, 01 Oct 2015 10:48:07 GMT

what is your out put ?

Re: count is not correct

arulsettu — Thu, 01 Oct 2015 10:49:13 GMT

check the sample app

Re: count is not correct

nagarjuna_kotha — Thu, 01 Oct 2015 10:50:05 GMT

IN YOUR FIRST CHAT DIMENSION IS CLASS(Age,6) , CHANGE IT TO age_band.

REGARDS,

Nagarjna

Re: count is not correct

arulsettu — Thu, 01 Oct 2015 10:51:01 GMT

i changed the app now

Re: count is not correct

avinashelite — Thu, 01 Oct 2015 10:54:04 GMT

Hi Arul,

I checked your file, problem is with the data. the same Employee_ID has more than one age attached to it to quote an example check 035 because of this your not able to get the right count.

Re: count is not correct

nagarjuna_kotha — Thu, 01 Oct 2015 10:54:07 GMT

As per your dashboard Data i m getting coorect.

Plz check my screen Shot.

Re: count is not correct

arulsettu — Thu, 01 Oct 2015 10:55:54 GMT

thats y taking distinct. any other way to get right value

Re: count is not correct

arulsettu — Thu, 01 Oct 2015 11:11:12 GMT

any ideas?

Re: count is not correct

Anonymous — Thu, 01 Oct 2015 11:25:05 GMT

Please try to check value for one age_band at DB & QV.

I think all the values displayed in the reports are correct.

Please check value for Age_band 0-6 in DB.

AGE	COUNT( {<[AGE] ={0,1,2,3,4,5,6}>} DISTINCT EMPLOYEE_ID)
	14433
0	2139
1	2903
2	3058
3	3051
4	2980
5	2944
6	2688

Re: count is not correct

Thu, 01 Oct 2015 11:34:34 GMT

Hey,

try the intervallMatch-Functionality of QlikView, this seems like a perfect problem for IntervallMatch.

You won´t need all thoses IFs there.

Re: count is not correct

jagan — Thu, 01 Oct 2015 12:30:10 GMT

Hi Arul,

If the employees has more than one age band then the employee is counted twice, to overcome this you have to flag the max age records and then create the age band. Like this you can eliminate the duplicate records.

Regards,

Jagan.

Re: count is not correct

arulsettu — Sat, 03 Oct 2015 06:56:25 GMT

can you please tell me how to do it. this my expected below there please check

Re: count is not correct

MarcoWedel — Sat, 03 Oct 2015 23:17:51 GMT

Hi,

maybe also helpful:

Custom Filter(unusual range)

regards

Marco

Re: count is not correct

arulsettu — Mon, 05 Oct 2015 04:08:05 GMT

Hi Macro,

can you please share how to do this with my sample app.

Thanks

Re: count is not correct

jagan — Mon, 05 Oct 2015 04:14:19 GMT

Hi Arul,

Share some sample data and your expected output, so that it would be easier to provide solution.

Regards,

Jagan.