Request Age Calculation

[Anku]

Hi all,

I need your help.

I have a data which consists request id movements into different departments and respective Date.

Sample:

I want to calculate tenure of each unique request id but not overall, instead the tenure that unique id spent at last department.

Output:

I am new to Qlik-sense, please help me how to do it in Qlik-sense.

Thank you!

[Anku]

Hi all,

I need your help.

I have a data which consists request id movements into different departments and respective Date.

Sample:

I want to calculate tenure of each unique request id but not overall, instead the tenure that unique id spent at last department.

Output:

I am new to Qlik-sense, please help me how to do it in Qlik-sense.

Thank you!

[Digvijay_Singh]

Something like this may be - 

[Kushal_Chawda]

@Anku  try below. make sure that your Date field is in proper date format.

data:
Load [Request ID],
     Department,
     Date
FROM Table;

Left Join(data)
Load [Request ID],
     FirstSortedValue(DISTINCT Department,-Date) as Department,
     1 as Latest_Dept_Flag
Resident data
Group by [Request ID];

Now, create a table with below dimensions and measure

Dimension:

1) Request ID

2) Department

Measure: (Age)

=max({<Latest_Dept_Flag={1}>}Date)- min({<Latest_Dept_Flag={1}>}Date)

[rwunderlich]

How about a table chart with:

Dimensions:
[Request ID]
Department

Measure:

if(Department = FirstSortedValue(Total<[Request ID]> Department, -Date), Max(Date) - Min(Date))

Uncheck "Include zero values" in Add-Ons, Data handling.

-Rob

[Anku]

@Kushal_Chawda thanks mate, it's working. 

one more favour I need please, as the request is moving between the departments, there's a possibility that it may end up at the dept which it may have been earlier as well. So in this case, max date is correct but not min date. Is there any way round to get the min date of the latest dept. Thank you for responding :).

[Anku]

@rwunderlich thank you sir. It's helpful.

I have a scenario added to above question.

as the request is moving between the departments, there's a possibility that it may end up at the dept which it may have been earlier as well. So in this case, max date is correct but not min date. Is there any way round to get the min date of the latest dept only. Thank you.

[Anku]

Thanks for sharing.

[Kushal_Chawda]

@Anku  could you share one example?

[Anku]

@Kushal_Chawda  like

refer request id2, desired output - 

Output I am getting:

Thanks!

[Kushal_Chawda · Accepted solution]

@Anku  try below

data:
Load [Request ID],
     Department,
     Date
FROM Table;

Final:
Load *,
     if(([Request ID]=Previous([Request ID]) and Department<>Previous(Department))
     or [Request ID]<>Previous([Request ID]),1,0) as dept_change_flag
Resident data
Order by [Request ID],Date;

Drop Table data;

Left Join(Final)
Load [Request ID],
     max(Date) as Date,
     1 as latest_dept_change_flag
Resident Final
Where dept_change_flag=1
Group by [Request ID];

drop field dept_change_flag;

Now, create a table with below dimensions and measure

Dimension:

1) Request ID

2) Department

Measure: (Age)

=max(Date)- min({<latest_dept_change_flag={1}>}Date)

Note:  Uncheck "Include zero values" option from properties of table in Add-on