YOS Grouping

[tracycrown]

Dear all

I need help on following problems:

1. Compute YOS (Year of Service) based on (Current Date - Date Joined)/365;

2. Group computed YOS in 1 under YOS Group known as (1-5, 6-10, 11-15, 16-20);

3. Compute Mininum, Average and Maximum Salary under YOS Group

Correct Answer should be as follows :

YOS Group    No. of Employee      Min         Avg           Max

1-5                          3                     950         1100          1250

6-10                        2                     750           805            860

11-15                      3                     650           750            850

16-20                      2                     850           875            900

Thank you very much

Tracy

[MarcoWedel]

Hi,

one idea (you still have to adjust the class function to match your requirment though):

LOAD *,
    Dual(Replace(Class(YOS,5,'',1),'<=  <','-'),Class(YOS,5,'',1)) as [YOS Group];
LOAD Badge,
    Gender,
    [Date Joined],
    Age(Today(), [Date Joined]) as YOS,
    Base
FROM [yourpatth/YOS Group-1.xls] (biff, embedded labels, table is [Survey Data$]);

hope this helps

regards

Marco

[MarcoWedel]

changed class representation for integer values:

Dual(SubField(Class(YOS,5,'',1),' <=  < ',1)&' - '&(SubField(Class(YOS,5,'',1),' <=  < ',2)-1), Class(YOS,5,'',1)) as [YOS Group];

Using the Age function, Date 09.09.2003 is grouped different from your (Today()-Date)/365 solution because this expression disregards leap-years.

hope this helps

regards

Marco

[tracycrown · Accepted solution]

Hi Macro

Many thanks for your help, please help to explain the following:

1. Please explain the Dual statement in details, why 5 and 1 and the logic to generate 6-10, 10-15;

2. How to generate Dual for 1-5, 6-10 instead of 1-5, 5-10?

Thank you very much for all your quick response and help.

Tracy

Date: Sun, 7 Sep 2014 12:25:14 -0400

From: qcwebmaster@qlikview.com

To: tracycrown@hotmail.com

Subject: Re: - Re: YOS Grouping

Qlik Community

Re: YOS Grouping

reply from Marco Wedel in New to QlikView - View the full discussion

changed class representation for integer values:

Dual(SubField(Class(YOS,5,'',1),' <= < ',1)&' - '&(SubField(Class(YOS,5,'',1),' <= < ',2)-1), Class(YOS,5,'',1)) as ;

Using the Age function, Date 09.09.2003 is grouped different from your (Today()-Date)/365 solution because this expression disregards leap-years.

hope this helps

regards

Marco

Reply to this message by replying to this email, or go to the message on Qlik Community

Start a new discussion in New to QlikView by email or at Qlik Community

Following Re: YOS Grouping in these streams:

Inbox

© 1993-2014 QlikTech International AB

Copyright & Trademarks | Privacy | Terms of Use | Software EULA

[MarcoWedel]

I created the YOS field with the age function rather than your expression. The dual function creates buckets with the size of 5 beginning at 1. (1<=x<6, 6<=x<10,...) because I wanted the minus sign in the group names instead, I replaced the <= < with it. For integer numbers < 6 means <=5, so I adjusted the max value of each group by subtracting one.

hope this helps

regards

Marco

P.S.: did you intentionally mark your own answer as correct?

[tracycrown]

Hi Marco

Thanks for your explanation but how can the logic knows that it should stop at say max 25?

Your logic has equal interval of 5 (1-5, 6-10 etc) but what if it is not equal interval (1-5, 6-200, 201-500 etc) , what should be the new logic ?.

Thank you very much

Tracy

Date: Mon, 8 Sep 2014 01:58:20 -0400

From: qcwebmaster@qlikview.com

To: tracycrown@hotmail.com

Subject: Re: - YOS Grouping

Qlik Community

YOS Grouping

reply from Marco Wedel in New to QlikView - View the full discussion

I created the YOS field with the age function rather than your expression. The dual function creates buckets with the size of 5 beginning at 1. (1<=x<6, 6<=x<10,...) because I wanted the minus sign in the group names instead, I replaced the <= < with it. For integer numbers < 6 means <=5, so I adjusted the max value of each group by subtracting one.

hope this helps

regards

Marco

P.S.: did you intentionally mark your own answer as correct?

Reply to this message by replying to this email, or go to the message on Qlik Community

Start a new discussion in New to QlikView by email or at Qlik Community

Following YOS Grouping in these streams:

Inbox

© 1993-2014 QlikTech International AB

Copyright & Trademarks | Privacy | Terms of Use | Software EULA