Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have created FY Calendar based on Emp DOJ and DOL.
Created caldates for each employee from DOJ to DOL
If Emp has no DOL then consider to till date.
This is working fine.
But performance wise I want reduce the data.I don't want to load all the cal dates.
how to accomplish this requirement?
PFB for your reference with my sample data.
NewCalDate which is Highlighted in Yellow is my desired output instead of load all the cal dates.
Any Ideas kindly Suggest.
Thanks for your effort and this was really helpful
I'll check with my data and get back to you!!!
Thanks again!!!
Yes almost closed.
but need few more changes.
Not getting Daywise date
For Emp 3359 DOJ(01-Dec-2016) and DOL(03-Dec-2016)
So It should show
Monthwise dates till 30-Nov-2017
Daywise dates
1-Dec-2016
2-Dec-2016
3-Dec-2016
But it is showing till Aug 2017
and
For Emp 3501 DOJ(01-Jan-2017) and no DOL
So it should show to till date(ie 08-Aug-2017)
Monthend dates
31-Jan-2017
28-Feb-2017
31-mar-2017
30-Apr-2017
31-May-2017
30-Jun-2017
31-Jul-2017
and day wise dates
1-Aug-2017
2-Aug-2017
3-Aug-2017
4-Aug-2017
5-Aug-2017
6-Aug-2017
7-Aug-2017
8-Aug-2017
In future if EMP moves to September then only show 31-Aug-2017 instead of all the dates of August.
and Show daywise Sep dates.
kindly help!!!
Well, so you want days for last month and monthends for all prior months, right?
Yes Tresesco!!!
That is exactly what I need.
If EMP DOJ is 26-Feb-2017 and no DOL
Daywise dates
26-Feb-2017
27-Feb-2017
28-Feb-2017
And Monthwise
31-Mar-2017
30-Apr-2017
31-May-2017
30-June-2017
31-Jul-2017
And Daywise dates
1-Aug-2017
2-Aug-2017
3-Aug-2017
4-Aug-2017
5-Aug-2017
6-Aug-2017
7-Aug-2017
8-Aug-2017
Yes Tresesco exactly.
and I did small change in your expression now that's working fine.
MonthEnd(Addmonths(DOJ,IterNo()-1)) as Cal_Date
While MonthEnd(Alt(Date#(DOL,'MM/DD/YYYY'),If(DOL='NA',today(),DOL)))>=MonthEnd(Alt(AddMonths(DOJ,iterno()-1))) ;
But not getting Daywise dates
Kindly look @ and suggest as I'm totally got stuck here
So for broken starting months also you would need days, is it?
Try something like:
Input:
Load
EMP,
Date#(DOJ,'MM/DD/YYYY') as DOJ,
Date#(DOL,'MM/DD/YYYY') as DOL
Inline [
EMP, DOJ, DOL
3359, 12/1/2015, 12/3/2016
3501, 1/1/2017, NA
A210, 8/26/2016, NA
];
NoConcatenate
FM:
//First broken month
Load
EMP,
DOJ,
DOL,
Date(If(Day(DOJ)=1, DOL,Date(DOJ+IterNo()-1))) as CalDate
Resident Input While if(Day(DOJ)=1,IterNo()=1,DOJ+IterNo()-1<=RangeMin(MonthEnd(DOJ),DOL));
LM:
//Last broken month
Load
EMP,
DOJ,
DOL,
Date(MonthStart(DOL)+IterNo()-1) as CalDate
Resident Input While MonthStart(DOL)+IterNo()-1<=DOL;
CM:
//Complete Months
Load
EMP,
DOJ,
DOL,
MonthEnd(Addmonths(DOJ,IterNo()-1)) as CalDate
Resident Input While MonthEnd(Alt(DOL,today()))>=MonthEnd(AddMonths(DOJ,iterno()-1));
Drop Table Input;
Note: Try to grasp the idea of the code instead of just copy-pasting it, so that if it needs a small adjustment in your context you can do it (I write so, because I guess that you would need to tweak a bit after rigorous testing on output. No pun intended.)