Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
@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.. 😞
@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])),
FirstSortedValue([Siuntimo data], Aggr([Siuntimo data]+Rank([Serviso pavadinimas])/1e10, [Bylos/Subbylos numeris], [Serviso pavadinimas], [Siuntimo data]), 2)
),
[Bylos/Subbylos numeris]))
@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]))
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?
@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? 😞
@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
@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 :😀