Announcements
cancel
Showing results for
Did you mean:
Creator

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

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!

20 Replies
Creator
Author

@Kushal_Chawda  1st sheet. Table below, the 4th column, don't know, maybe I need to make an example .qvf file with english names?

The idea is, I want results for every USERID with some calculations, for example, how much did they got ID's, how much of the ID's are DONE and etc. The 4th columns is how much networkdays between 1st response.

The last sheet with name "Detali" is the excel sheet

@sunny_talwarThanks! But not working.. 😞

MVP

@Edvin  Is this what you want?

This is what I used

``````Avg(Aggr(
NetWorkDays(
FirstSortedValue([Siuntimo data], Aggr([Siuntimo data]+Rank([Serviso pavadinimas])/1e10, [Bylos/Subbylos numeris], [Serviso pavadinimas], [Siuntimo data]), 2)
),
[Bylos/Subbylos numeris]))``````

Creator
Author

@sunny_talwar  probably yes, thanks! I'm gonna check if it works correctly, but I think it will. My only question, what if there's no second lowest date? What if we don't have the response, will it take into account as '0' for average, or it's just NULL?

@Edvin  one change I did is to removed total. Does it gives correct at that level? What would be the output if you expand the second

avg(aggr(NetWorkDays(min(total <[Bylos/Subbylos numeris]>{<DateFlag={1}>}[Siuntimo data]),
min(total <[Bylos/Subbylos numeris]>{<DateFlag={2}>}[Siuntimo data])), [Bylos/Subbylos numeris]))

MVP

I am not entirely sure, but my guess is that it will try to call it null, but I might be wrong. @Kushal_Chawda or @Taoufiq_Zarra  do you guys know this?

Creator
Author

@sunny_talwar  Oh, if you can check for the ID number = '2415235', why your expression shows '-' when the second lowest date and the lowest date are the same? 😞

MVP

@Edvin Updated expression

``````Avg(Aggr(
NetWorkDays(
FirstSortedValue(DISTINCT [Siuntimo data], Aggr([Siuntimo data]+Rank(TOTAL [Serviso pavadinimas])/1e10, [Bylos/Subbylos numeris], [Serviso pavadinimas], [Siuntimo data])),
FirstSortedValue(DISTINCT [Siuntimo data], Aggr([Siuntimo data]+Rank(TOTAL [Serviso pavadinimas])/1e10, [Bylos/Subbylos numeris], [Serviso pavadinimas], [Siuntimo data]), 2)
),
[Bylos/Subbylos numeris]))``````

Also, noticed that same date within NetWorkDays() function gives 1. If you want one less day, then do -1 at the end

Creator
Author

@Kushal_Chawda @sunny_talwar  thanks both, guys!

I would accept both your answers for solution, but sunny's expression work, without editing back-end.

Really appreciate, thanks again!

@sunny_talwar  Your updated expressions exactly gives same result as mine with bit of script logic. I would prefer to go with script logic Flag because firstsortedvalue function is always the resource consuming functions. So better to avoid the usage of it as far as possible. Bu I think @Edvin  can confirm if this figures are correct or not.

@Edvin  You said you will accept both solution but you did not :😀