topic Re: YOS Grouping in QlikView

YOS Grouping

tracycrown — Sun, 07 Sep 2014 14:28:20 GMT

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

Re: YOS Grouping

MarcoWedel — Sun, 07 Sep 2014 14:55:39 GMT

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

Re: Re: YOS Grouping

MarcoWedel — Sun, 07 Sep 2014 16:25:02 GMT

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

Re: YOS Grouping

tracycrown — Mon, 08 Sep 2014 01:18:15 GMT

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

Re: YOS Grouping

MarcoWedel — Mon, 08 Sep 2014 05:57:48 GMT

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?

Re: YOS Grouping

tracycrown — Wed, 10 Sep 2014 03:12:42 GMT

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

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