Calculate consecutive working days

building reports webintelligence
1

Dear Gurus,

I’ve a peculiar requirement where I need to calculate consecutive working days for employees.
Info I’ve is:
Employee, Calendar Day. Calendar day is only populated when the employee goes to work. If not it is blank.

Based on calendar day … if an employee worked consecutive days, I need to count each day as 1 and sum up to get number of days consecutively worked in a month.

for ex:

Please refer the screenshot attached.

Please kindly help as it is killing me :hb:

Thanks a million in advance.

Regards,
Ravi Kanth
Consecutive days.jpg

ravismiles4u (BOB member since 2015-07-24)

2

Briefly.

You might be able to do this using the Previous() function and then checking whether the date you have is only one day away from the Previous() value in that dimension and then setting a flag to 1, then adding up the flags.

Pseudo

=If(DaysBetween([Date];Previous([Date])=-1) Then 1 Else 0

Something like that, not got Webi to hand.

ABILtd :uk: (BOB member since 2006-02-08)

3

Hi Andy,

With your formula, I can get only 1 in all the rows. I want to get runningsum but it should become zero when the difference is not 1 day.
For Ex:

01.01.2011 - 1
05.01.2011 - 0
06.01.2011 - 1
07.01.2011 - 2
.
.
.
.
15.01.2011 - 10
20.01.2011 - 0
21.01.2011 - 1
22.01.2011 - 2
23.01.2011 - 3
.
.
.
31.01.2011 - 11

like this.

Please help me.

Thanks,
Ravi Kanth

ravismiles4u (BOB member since 2015-07-24)

4

That wasnt an actual formula to use it was some pseudo code to give you an idea of how you might start achieving this with the previous function. Youll have to experiment.

ABILtd :uk: (BOB member since 2006-02-08)

5

Thinking outloud, well, on the blog, and so I have not tested it. Can you take Andy’s first part and add running count?? NO promises, just a thought…

=If(DaysBetween([Date];Previous([Date])=-1) Then RunningCount([Date]) Else 0

Again, just wondering if by chance it would work.

bmorehappy (BOB member since 2012-06-23)

6

I thought about that but as we dont have a dimension to reset on as such its a bit unworkable!

ABILtd :uk: (BOB member since 2006-02-08)

7

Hi Andy,

you 100% correct. If we use RunningCount. we need to have reset counter.
But I don’t think calendar day can be used as reset. If can, please let me know the formula for reset_dims as I tried and it is not working.

If we you running count… system is taking from 1st line and continuously populating the counter.

Thanks & Regards,
Ravi Kanth

ravismiles4u (BOB member since 2015-07-24)

8

I have a close solution.

Create a variable (referred to as [rst]):

=RunningCount([Date] Where (DaysBetween(Previous([Date]);[Date])>1))

Add this to your block, use it as a break (and remove header and footer, then apply table header).
Next, another variable:

=Rank([Date];Bottom)

Should give following results (attached).

You’ll need to hide the [rst] column using white text on white background, or whatever you’re comfortable with.

HTH

NMG
229767.JPG

mcnelson :uk: (BOB member since 2008-10-09)

9

Hi Nelson,

Superb! Thank you for the solution.
But I’m really sorry to say that I couldn’t provided my entire requirement.

Actually my requirement is to calculate:

Number of employees who are consecutively working >= 12 days and <= 26 days.

For Ex: if there are 100 employees in a company then if 30 employees work consecutively 25 days and 50 employees work consecutively >= 12 days.

Then my report should show like below:

Employee #
100

of employees work >= 12days

50

of employees work <= 26 days

30

Let me know if you couldn’t understand my requirement.

Thanks a million.
Ravi Kanth

ravismiles4u (BOB member since 2015-07-24)

10

Yes, I understand your requirement, but that’s not what you originally posted, which was this:

My advice would be to push the calculation back to the ETL or database layer.

NMG

mcnelson :uk: (BOB member since 2008-10-09)

11

Sorry Nelson.
I was thinking that if I could get the solution which you have provided then I could calculate later. But after your excellent solution, my requirement is quite complex which cannot be done @ webi level.

I really appreciate your effort… :+1: :+1: :+1:

Thanks a ton again

ravismiles4u (BOB member since 2015-07-24)