Need to display wait time

[shabarish0587]

Hi Folks,

              I am facing one issue like i want to display wait days between dates. actually i have columns like F_Value,U_Value,Creation_Date,Completed_Date by using these columns i want to display active days and wait days.

For example :

F_Value	U_Value	Create_Date	Completed_Date	Active Days	Wait Days
F101	U121	01-01-2022	10-01-2022	10	1
F101	U231	12-01-2022	15-01-2022	4	2
F101	U211	18-01-2022	23-01-2022	6	4
F101	U311	28-01-2022	31-01-2022	4

if we observe above example i want to get active days and wait days. how we are getting active days ,based of U_Value create_Date and Completed_Date can generate. so here for U121 i have taken (completed_Date-Creation_Date) like (10-01-2022-01-01-2022) we will get 10 days that is we are displaying in active days column. For one F_value can have mutiple U_Values so here first U_Value has started on 01-01-2022 and ended on 10-01-2022 and second u_value like U231 has started on 12-01-2022 and ended on 15-01-2022 here we need to take wait time between first_completed u_value and second u_value started like (12-01-2022-10-01-2022) will get 1 day wait time between these two dates. i want to display this wait time data. can anyone help me on this to get wait time data . i am sharing sample excel file with examples.

[Hill69]

---

@shabarish0587 wrote: DMVNow

Hi Folks,

              I am facing one issue like i want to display wait days between dates. actually i have columns like F_Value,U_Value,Creation_Date,Completed_Date by using these columns i want to display active days and wait days.

For example :

F_Value	U_Value	Create_Date	Completed_Date	Active Days	Wait Days
F101	U121	01-01-2022	10-01-2022	10	1
F101	U231	12-01-2022	15-01-2022	4	2
F101	U211	18-01-2022	23-01-2022	6	4
F101	U311	28-01-2022	31-01-2022	4

if we observe above example i want to get active days and wait days. how we are getting active days ,based of U_Value create_Date and Completed_Date can generate. so here for U121 i have taken (completed_Date-Creation_Date) like (10-01-2022-01-01-2022) we will get 10 days that is we are displaying in active days column. For one F_value can have mutiple U_Values so here first U_Value has started on 01-01-2022 and ended on 10-01-2022 and second u_value like U231 has started on 12-01-2022 and ended on 15-01-2022 here we need to take wait time between first_completed u_value and second u_value started like (12-01-2022-10-01-2022) will get 1 day wait time between these two dates. i want to display this wait time data. can anyone help me on this to get wait time data . i am sharing sample excel file with examples.

---

I am facing the same issue, did you found any solution for that?

[Mark_Little]

Hi there,

I don't quite understand how you are getting the the second value wait days, but active days is as simple as 

Completed_Date - Create_Date. this can be done in script or front end.

[Rajashekar · Accepted solution]

hi @shabarish0587 

you can use the previous or peek function but the only thing is you will get the value in the next row. Here is some thin I tried. 

Test:
LOAD
F_Value,
U_Value,
Create_Date,
Completed_Date
FROM [lib://Desktop/ActiveDays_WaitDays.xlsx]
(ooxml, embedded labels, table is Sheet1)

;

NoConcatenate

Load *,
if(F_Value = Previous(F_Value),Create_Date - Peek(Completed_Date), 0) as WaitingTime

Resident Test
Order by F_Value, Create_Date;

Drop Tables Test;

result: