Solved: Re: Cal_FY - Qlik Community

antonybi · ‎2017-08-08

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.

tresesco · ‎2017-08-08

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.)

View solution in original post

tresesco · ‎2017-08-08

Is there any logic how you arrived to your desired dates, or they are just coming from a field/list may from a transaction table? How does your script look like now?

antonybi · ‎2017-08-08

Hi Tresesco,

Thanks for your quick response.

CalDate which I derived based on the employee DOJ and DOL.

It generates all the dates based on every employee DOJ and DOL.

But performance wise I want to reduce the data.I don't want to load all the dates as per NewCalDate which I have highlighted in yellow.

Is it possible to achieve this requirement??

tresesco · ‎2017-08-08

Let's talk about what you have and what you want, not what you derived that you don't want. How does your source table look like and how do you want the output to be looked like?

antonybi · ‎2017-08-08

PFA, Source data it contains only EMP, DOJ and DOL

tresesco · ‎2017-08-08

How do you calculate 15 dates (CalDate) from two dates, logic?

antonybi · ‎2017-08-08

The logic is,

DOJ of Emp 3359 is 1-Dec-2015 and DOL is 03-Dec-2016

With my existing, script I can generate the CalDates between 1-Dec-2015 to 03-Dec-2016.

But I want to generate dates as like below

Create only Monthend date of these below months instead of all the dates

Dec-2015

Jan-2016

Feb-2016

Mar-2016

Apr-2016

May-2016

Jun-2016

Jul-2016

Aug-2016

Sep-2016

Oct-2016

Nov-2016

And now Day wise

1-Dec-2016

2-Dec-2016

3-Dec-2016

Hope you got this logic!!!

tresesco · ‎2017-08-08

And where there is no DOL you would set the date as Jun-2017?

antonybi · ‎2017-08-08

That is I have restricted to Jun-2017

Usually

If Emp has no DOL It indicates is in active, For this case, consider from DOJ to till date(today()).So it is obviously 08-Aug-2017.

tresesco · ‎2017-08-08

Try like:

Load

EMP,

DOJ,

DOL,

MonthEnd(Addmonths(DOJ,IterNo()-1)) as CalDate

While MonthEnd(Alt(Date#(DOL,'MM/DD/YYYY'),today()))>=MonthEnd(Alt(AddMonths(DOJ,iterno()-1))) ;

Load * Inline [

EMP, DOJ, DOL

3359, 12/1/2015, 12/3/2016

3501, 1/1/2017, NA

A210, 8/26/2016, NA

]