Counting days between two dates, but wrong answer, when the dates are the same day

[Edvin]

Hello all,

Got a problem. I'm counting days between two dates, I need to count how many network days are between the lowest date and the second to the lowest, grouped by ID.

The example:

ID	Company name	Process	Send date
2415976	JMA	SENT	2020.06.11
2415976	LD	NOT	2020.06.11
2415976	JMA	SENT	2020.07.09
2415976	LD	NOT	2020.07.09
2415976	JMA	SENT	2020.07.10
2415976	LD	DONE	2020.07.10

My measure expression looks like this:

avg(networkdays(aggr(min([Send date]),[ID]),aggr(min([Send date],2),[ID])))

Now, somehow it skips the second date, which is equal to the 1st one '2020.06.11', and brings the result 21, which is between '2020.07.09' and '2020.06.11'

The outcome should look like this:

ID	NetWorkDaysBetween
2415976	0

Thanks for the help!

[Taoufiq_Zarra]

Hi @Edvin ,

1-NetWorkDaysBetween two date

if two dates are equal Networkday will return 1 if it's a working day otherwise 0.

Example :

=networkdays ('30/09/2020', '30/09/2020') ->1

=networkdays ('03/10/2020', '03/10/2020')->0

2- for the problem I propose you another alternative

for the first min use :

=Date(textbetween(concat(
aggr(nodistinct min([Send date]),ID,[Send date]),','),'',','))

and for the second min use :

=Date(textbetween(concat(
aggr(nodistinct min([Send date]),ID,[Send date]),','),',',','))

So the networdday script wil be :

=networkdays(
Date(textbetween(concat(
aggr(nodistinct min([Send date]),ID,[Send date]),','),'',',')),
Date(textbetween(concat(
aggr(nodistinct min([Send date]),ID,[Send date]),','),',',','))
)

I tested for this example :

and also for this :

[Kushal_Chawda]

@Edvin  I would recommend to create Flag in script to make your expression simple and easy to understand. Also it will be good as performance point of view.

Data:
LOAD ID, 
     [Company name], 
     Process, 
     date(Date#([Send date],'YYYY.MM.DD')) as [Send date]
FROM
[Book2.xlsx]
(ooxml, embedded labels, table is Sheet1);

Final:
NoConcatenate
LOAD *,
     if(ID<>Peek(ID),1,RangeSum(Peek(DateFlag),1)) as DateFlag
Resident Data
Order by ID,[Send date];

DROP Table Data;

Now you can simply use below expression

=NetWorkDays(Only({<DateFlag={1}>}[Send date]),Only({<DateFlag={2}>}[Send date]))-1

or 

=NetWorkDays(Only({<DateFlag={1}>}[Send date]),Only({<DateFlag={2}>}[Send date]))

[Edvin]

@Taoufiq_Zarra  Thanks for the help, but that doesn't work. If two dates are equal and it's a working day, it doesn't give me the result 1, that's the problem. And your alternative solution doesn't work too.

@Kushal_ChawdaSomething's wrong. I used the 1st expression, and it only shows for the TOTAL line the correct answer..

Also, I got lots of ID number's so I need it averaged, but when I do it with aggr(avg(EXPRESSION),[ID]) it doesn't bring anything.

[Kushal_Chawda]

@Edvin  what is the dimensions you are using. 

[Edvin]

I've got [ID], [Event date], [First sent date], [Company city], [Company name], [Send date],[Process].

Not sure what it will give you.

Maybe it is better to attach .qvf file?

[Kushal_Chawda]

@Edvin  If you can attach Qvf that will be helpful. try below meanwhile f you are following the approach I suggested

=avg(total <ID>aggr(NetWorkDays(Only(total <ID>{<DateFlag={1}>}[Send date]),Only(total <ID>{<DateFlag={2}>}[Send date])), ID))

[sunny_talwar]

@Edvin May be try this

NetWorkDays(
	FirstSortedValue([Send date], Aggr([Send date]+Rank([Company name])/1e10, ID, [Company name], [Send date])),
	FirstSortedValue([Send date], Aggr([Send date]+Rank([Company name])/1e10, ID, [Company name], [Send date]), 2)
)

[Edvin]

@Kushal_Chawdathanks! It works, but not as intended fully.

I've got one more problem, for every ID there is a responsible USER, for which I'm trying to calculate this expression. How much days (on average) do we get the 1st response (network days between those two dates).

The last column is the one I did with your expression, it's the same value for everyone.

Gosh... It's so complicated to just calculate networkdays between two dates :D.

If it's impossible to help, tell me, I will attach .qvf file

[Kushal_Chawda]

@Edvin  I would advise to attach qvf