topic Re: Request Age Calculation in App Development

Request Age Calculation

Anku — Sat, 11 Sep 2021 10:33:35 GMT

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!

Re: Request Age Calculation

Digvijay_Singh — Sat, 11 Sep 2021 14:17:49 GMT

Something like this may be -

Re: Request Age Calculation

Kushal_Chawda — Sat, 11 Sep 2021 17:51:41 GMT

@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)

Re: Request Age Calculation

rwunderlich — Sat, 11 Sep 2021 17:24:11 GMT

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

Re: Request Age Calculation

Anku — Sun, 12 Sep 2021 09:22:53 GMT

@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 :).

Re: Request Age Calculation

Anku — Sun, 12 Sep 2021 09:31:28 GMT

@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.

Re: Request Age Calculation

Anku — Sun, 12 Sep 2021 09:33:53 GMT

Thanks for sharing.

Re: Request Age Calculation

Kushal_Chawda — Sun, 12 Sep 2021 10:15:41 GMT

@Anku could you share one example?

Re: Request Age Calculation

Anku — Sun, 12 Sep 2021 11:41:29 GMT

@Kushal_Chawda like

refer request id2, desired output -

Output I am getting:

Thanks!

Re: Request Age Calculation

Kushal_Chawda — Sun, 12 Sep 2021 13:00:40 GMT

@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

Re: Request Age Calculation

Anku — Sun, 12 Sep 2021 16:49:33 GMT

@Kushal_Chawda You're master buddy. Thank you so much.