count is not correct

[arulsettu]

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.

[arulsettu]

this is the expected output

14594

0 to 6 age

15088

7 to 18 age 61451 19 to 30 age

74968

31 to 45 age 24283 46 to 60 age 2423above 60 age

please check my sample app above

[arulsettu]

is this enough jagan na

[arulsettu]

one thing here employee_id have multiple age entry. because it has various policy id.

like this

emp_id,age,pol_id

035	34	57981
035	30	63873
035	24	69281
035	35	70361
035	25	72759
035	31	76244
035	40	79043
035	36	80227
035	26	80587
035	34	83714

please share some idea

Thanks

[jagan]

Hi Arul,

Try something like this

Data_Temp:

LOAD

*

INLINE [

emp_id,age,pol_id

035, 34, 57981

035, 30, 63873

035, 24, 69281

035, 35, 70361

035, 25, 72759

035, 31, 76244

035, 40, 79043

035, 36, 80227

035, 26, 80587

035, 34, 83714];

Data:

LOAD

emp_id, age, pol_id, flag,

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

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

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

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

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

     if(age>=60,'60')))))) as age_band;

LOAD

emp_id, age, pol_id,

If(Peek('emp_id') <> emp_id, 1, 0) AS flag

RESIDENT Data_Temp

ORDER BY emp_id, age desc, pol_id;

DROP TABLE Data_Temp;

Now in chart use like this:

Dimension: age_band

Expression: Count({<flag={1}>} emp_id)

Hope this helps you.

Regards,

Jagan.

[arulsettu]

getting same old output.

[arulsettu]

if i select particular policy id the count of emp id with age wise is correct. but over all count shows wrong.

what will be the issue?

Thanks

[arulsettu]

any idea to solve this. its emergency.

Thanks

[jagan]

Hi,

Arul come up with a clear example, take one emp_id with multiple ages and your expected age_band for this emp_id, then only it is easier to solve this. 

Regards,

Jagan.

[arulsettu]

if i choose emp id 035

chart showing like this

but the expected output is

emp, age

035	34
035	30
035	24
035	35
035	25
035	31
035	40
035	36
035	26
035	34

[#11]

Use Sum() instead of Count().