Grouping Experience

[#1]

Hi Friends,

I have a field of experience from 1 to 20 yrs. I want to group the experience as 1-5 yrs, 6-10 yrs and 10+ yrs showing the number of emps laying in it.

thanx

[swuehl]

LOAD     

     Experience,

     if(Experience <6, dual('1-5 yrs',5),

          if(Experience < 11, dual('6-10 yrs', 10), dual('10+ yrs', 20) ) )     as ExpGroup

FROM ...;

Then use ExpGroup as dimension and count(ExpGroup) as expression.

[dmohanty]

Hi AB,

You can do this in the Expression of the list Box as well. Add the field Years from Experience table in a List Box, go to expression and put the below script:

=

if(Years <6, DUAL('1-5 Years',5),

if(Years < 11, DUAL('6-10 Years', 10),

DUAL('10+ Years', 20) ) )

[Anonymous]

Try calculated dimention:

if ( Year >= 10, '10+', Class( Year, 5 ) )

Remember to sort by Year

[#5]

Hi Swuehl,

I was trying yr code but the EmpGroup is not creating. I am attaching the snap shot of the code. Plz chk it.

[#6]

I have fields Emp name, Exp, rating and few more. I am making a pie chart, which drill down from Rating to Experience. In Experience it shows the number of employees possessing the rating. 

[Anonymous]

Hi Arindam,

If you create a calculated dimention with Class, it will work as you is describing.

Use this as the dimention code:   if ( Year >= 10, '10+', Class( Year, 5 ) )

You can also use this in a hierarchy, so you can do your Drill.

[swuehl]

Since the Employee's experience is static, I would suggest to classify the experience in the script, though it should work also using a calculated dimension.

I haven't understood why you are using the

If Experience <= 5 then Load ... part

I would just use my code snippet above, and add your original fields EmpName, rating etc.:

LOAD     

     [Emp name],

     Exp,

     if(Exp <6, dual('1-5 yrs',5),

          if(Exp < 11, dual('6-10 yrs', 10), dual('10+ yrs', 20) ) )     as ExpGroup,

     rating,

     ..... // your other fields to be included here

FROM EmpDetail_New.xlsx;

It's probably easier to help you if you could post a small sample app or your excel file.

[#9]

Thanks Swuehl,

I am attaching the file, your idea is working but it is adding a more line of experience,

[swuehl · Accepted solution]

I would start with just 2 tables:

Tab1:

LOAD [Emp Name],

     EmpID,

     Address,

     DOB,

     Education,

     Experience,

     If(Experience<6,Dual('1-5 yrs', 5),

     if(Experience<11,Dual( '6-10 yrs',10),dual('10+ yrs',20))) as ExpGroup

from

EmpDetail_New.xlsx

(ooxml, embedded labels, table is Data);

Tab2:

CrossTable(Year, Data)

LOAD EmpID,

     [2009],

     [2010],

     [2011],

     [2012],

     [2013]

FROM

EmpDetail_New.xlsx

(ooxml, embedded labels, table is Rating);

These two tables will be linked by EmpID. This might be just ok, as far as I see.

Why do you want them concatenated into one table?