Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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?
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