topic Cal_FY in QlikView

Cal_FY

antonybi — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Cal_FY

tresB — Tue, 08 Aug 2017 05:59:56 GMT

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?

Re: Cal_FY

antonybi — Tue, 08 Aug 2017 06:08:15 GMT

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??

Re: Cal_FY

tresB — Tue, 08 Aug 2017 06:17:56 GMT

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?

Re: Cal_FY

antonybi — Tue, 08 Aug 2017 07:19:49 GMT

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

Re: Cal_FY

tresB — Tue, 08 Aug 2017 07:34:57 GMT

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

Re: Cal_FY

antonybi — Tue, 08 Aug 2017 07:49:06 GMT

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!!!

Re: Cal_FY

tresB — Tue, 08 Aug 2017 07:53:19 GMT

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

Re: Cal_FY

antonybi — Tue, 08 Aug 2017 08:04:08 GMT

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.

Re: Cal_FY

tresB — Tue, 08 Aug 2017 08:11:57 GMT

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

]

Re: Cal_FY

antonybi — Tue, 08 Aug 2017 08:19:59 GMT

Thanks for your effort and this was really helpful

I'll check with my data and get back to you!!!

Thanks again!!!

Re: Cal_FY

antonybi — Tue, 08 Aug 2017 10:05:03 GMT

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!!!

Re: Cal_FY

tresB — Tue, 08 Aug 2017 10:12:39 GMT

Well, so you want days for last month and monthends for all prior months, right?

Re: Cal_FY

antonybi — Tue, 08 Aug 2017 10:24:22 GMT

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

Re: Cal_FY

antonybi — Tue, 08 Aug 2017 10:52:12 GMT

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

Re: Cal_FY

tresB — Tue, 08 Aug 2017 11:04:12 GMT

So for broken starting months also you would need days, is it?

Re: Cal_FY

tresB — Tue, 08 Aug 2017 11:28:50 GMT

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