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]

hi jagan mohan,

              can you please check it and suggest some solution. its kind of urgent

Thanks

[arulsettu]

can anyone suggest some idea?

Thanks

[jagan]

Hi Arul,

In your file there is only one record for Emp ID. 035 (Age band: 19-30), how you are expecting it as

age          count

19 to 30    4

31 to 46    6

Not sure about your requirement, can you come up with sample raw data for this employee instead of all the data and explain the logic.

Regards,

Jagan.

[arulsettu]

sure

[nagarjuna_kotha]

u Cannot get that out put bcoz in you chat your expression was Count on Emplyee-ID.If select one emp_id it will show only one .

The problem is with data .

Regards,
Nag

[arulsettu]

here is the data. here i need to count the employee_id for year 2015 by age band wise. master calendar created using approval_date from policy table.

[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

[arulsettu]

need anymore information jagan

Thanks

[arulsettu]

hi

     jagan approval_date is like this 03-02-2015 09:39:22

so i used convert like this

date(Floor(MP_APPR_DT)) as APPROVED_DATE

is this cause the issue? please help on this issue

Thanks

[arulsettu]

hi jagan mohan

do you need any other information. pls check the data.if you need any information pls let me know