topic Days allocation with 2 dates in QlikView

Days allocation with 2 dates

Fri, 13 Jun 2014 09:27:39 GMT

Hi everyone, I need your help. I’m working on a development about absenteeism, and I stuck with the actual data that I have.

At this day, my data is like this :

id_abs	B_date_abs	T_date_abs
12354	01/01/2014	20/04/2014
1454	23/02/2014	25/03/2014
8454	30/03/2014	01/04/2014

The goal is to analyse the % of absenteeism regarding the month selected or the year. So, for the first line, we've got 31 days of absenteeism in January, and 20 in february.

If I want to see the amount of days in february, it'll be 20 days (1st row) + 6 days (2nd row). What will be the best way to allocate those days for each months ?

Thanks a lot in advance.

Re: Days allocation with 2 dates

MK_QSL — Fri, 13 Jun 2014 09:42:52 GMT

Can you please explain little more? Don't get idea of your requirements from this description.

Re: Days allocation with 2 dates

sujeetsingh — Fri, 13 Jun 2014 10:02:53 GMT

Ramet,

What i will suggest you is that please explain in simple words and in synchronised steps the complete issue.

Explain what is the role of these two date field in calculating the Absent days.

Re: Days allocation with 2 dates

Fri, 13 Jun 2014 10:05:52 GMT

I want to know how many days of absence we had in the company by months. But I only have the date of the beginning of the sickness leave and the ending date.

For the 1st row, we've got 110 days of absence (01/01/2014 --> 20/04/2014). But it has to be divided like this :

id_abs	B_date_abs	T_date_abs	count_days
12354	01/01/2014	31/01/2014	31
12354	01/02/2014	28/02/2014	28
12354	01/03/2014	31/03/2014	31
12354	01/04/2014	20/04/2014	20

Re: Days allocation with 2 dates

rbecher — Fri, 13 Jun 2014 10:08:41 GMT

Then you have to create those records in script, I would suggest.

Re: Days allocation with 2 dates

Fri, 13 Jun 2014 10:15:42 GMT

Sure, but I don't know how to do it. That was the motivation for my post (bad-explained, of course ).

Does someone have an idea about it ?

Thx

Re: Days allocation with 2 dates

MK_QSL — Fri, 13 Jun 2014 11:15:31 GMT

Like this?

For simplicity, I have changed Field Names, You can change as per your database.

Re: Re: Days allocation with 2 dates

rbecher — Fri, 13 Jun 2014 11:23:14 GMT

The best and fastest solution is a While LOAD (with a little bit date gymnastics here):


Data:
LOAD * INLINE [
    id_abs, B_date_abs, T_date_abs
    12345, 01.01.2014, 20.04.2014
    1454, 23.02.2014, 25.03.2014
    8454, 30.03.2014, 01.04.2014
];

Result:
NOCONCATENATE LOAD 
id_abs,
MonthName(B_date_abs, IterNo()-1) as month,
If(AddMonths(B_date_abs, IterNo()) > T_date_abs, 
  T_date_abs - AddMonths(B_date_abs, IterNo()-1) +1,
  AddMonths(B_date_abs, IterNo()) - AddMonths(B_date_abs, IterNo()-1)) as count_days
Resident Data
While AddMonths(B_date_abs, IterNo()-1) < T_date_abs;

And have fun coding things like that.. 😉

- Ralf

Re: Re: Re: Days allocation with 2 dates

rbecher — Fri, 13 Jun 2014 11:55:59 GMT

My first shot was wrong. 😉

Data:
LOAD * INLINE [
    id_abs, B_date_abs, T_date_abs
    12345, 01.01.2014, 20.04.2014
    1454, 23.02.2014, 25.03.2014
    8454, 30.03.2014, 01.04.2014
];

Result:
NOCONCATENATE LOAD 
id_abs,
MonthName(B_date_abs, IterNo()-1) as month,
If(IterNo()=1, 
  Floor(MonthEnd(B_date_abs)) - B_date_abs,
  If(MonthStart(AddMonths(B_date_abs, IterNo()-1)) = MonthStart(T_date_abs), 
    T_date_abs - MonthStart(T_date_abs), 
    Floor(MonthEnd(AddMonths(B_date_abs, IterNo()-1))) - MonthStart(AddMonths(B_date_abs, IterNo()-1)))) +1 as count_days
Resident Data
While MonthStart(AddMonths(B_date_abs, IterNo()-1)) <= MonthStart(T_date_abs);

- Ralf

Re: Days allocation with 2 dates

Fri, 13 Jun 2014 12:53:14 GMT

It's perfect ! Thanks a lot !

Re: Days allocation with 2 dates

marinasbc — Fri, 30 Jan 2015 16:12:48 GMT

Hi Manish,

This is really useful, thanks. Any chance you can shed some light on how to do this with hour detail?

I managed to work out the proportion in an expression but I can only use it when analysing by employee and month EmpHours_in_this_month = ( Total_EmpHours / Total_EmpDays) / EmpDays_in_this_month

I would like to do this on the script, can anyone help?

Thanks

Best Regards

Marina