Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Days allocation with 2 dates

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_absB_date_absT_date_abs
1235401/01/201420/04/2014
145423/02/201425/03/2014
845430/03/201401/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.

1 Solution

Accepted Solutions
MVP
MVP

Like this?


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

View solution in original post

10 Replies
MVP
MVP

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

Master III
Master III

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.

Not applicable

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_absB_date_absT_date_abscount_days
1235401/01/201431/01/201431
1235401/02/201428/02/201428
1235401/03/201431/03/201431
1235401/04/201420/04/201420
MVP & Luminary
MVP & Luminary

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

Vizlib Head of R&D
Not applicable

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

MVP
MVP

Like this?


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

View solution in original post

MVP & Luminary
MVP & Luminary

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

Vizlib Head of R&D
MVP & Luminary
MVP & Luminary

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

Vizlib Head of R&D
Not applicable

It's perfect ! Thanks a lot !