topic Re: Claculating net working days in QlikView

Claculating net working days

Mon, 13 Apr 2015 11:01:26 GMT

Hi,

I have columns of data that I am importing

Claim no

Status

Last change date

the last change date is not time stamped.

how would I go ahead to calculate the net working days between specific Statuses.

what graph would be the best to use?

It is very easy to do in excel, but I would like to automate it in qlikview.

Re: Claculating net working days

robert_mika — Mon, 13 Apr 2015 11:47:57 GMT

Could you post sample of your data?

Re: Claculating net working days

amit_saini — Mon, 13 Apr 2015 11:52:05 GMT

Hi,

Please come up with some example/data and what are you looking for ????(result)

Thanks,
AS

Re: Claculating net working days

Mon, 13 Apr 2015 12:08:43 GMT

Hi,

attached is the detail that I import.

I would like to calculate the time it has taken from the one status to the other.

But the users only want performance between specific statuses, thus I need to specify the status.

Re: Claculating net working days

Mon, 13 Apr 2015 12:36:39 GMT

Hi Gerald,

have a look at the attached, should help with your issue.

hope that helps

Joe

Re: Claculating net working days

jpenuliar — Mon, 13 Apr 2015 12:41:40 GMT

Hi Gerald,

The attached file looks like a historical data of Claim Status.

Since you mentioned of your users having specific status to compute for networkdays, It will be easier to create a table where you have below fields:

ClaimNo, 1stStatusDate, 2ndStatuDate, Networkdays()

Re: Claculating net working days

gerry_hdm — Mon, 13 Apr 2015 12:51:06 GMT

Hallo Gerald

here is a Methode

I hope so , it is true, it would be good if you could number the rows Duch then man could better determine when each process when taking place. Then you can sort the data and read the predecessors.

Im from German sorry

have fun

Greatings Gerold

Re: Claculating net working days

Mon, 13 Apr 2015 12:53:29 GMT

Hi Joe,

almost but not quite.

So for example, "First Notice of loss" is the first status that is selected. and then "Claim registered", and then "assessor approved". this is the requirement from users.

I am going to need the ability to select which "statuses" to compare.

attached is the excel version of how I calculated the networkdays.

column U to AB

Re: Claculating net working days

Mon, 13 Apr 2015 12:57:40 GMT

Hi Gerry,

you method could work, except for the fact that there are a lot of statuses, and the user only requires reporting on specific statuses. it would become an issue to number each status.

Re: Claculating net working days

Mon, 13 Apr 2015 12:59:50 GMT

Hi Jonathan,

Yes, possible, but if you look at the Performance reporting workbook I attached just now, there are so many statuses, and there request change as need arises.

Re: Claculating net working days

gerry_hdm — Mon, 13 Apr 2015 13:09:13 GMT

Hello Gerald

hi could you also provide kernel a timestamp instead of the date. Then you had the correct sequence

greatings from Germany

Gerold

Re: Claculating net working days

Mon, 13 Apr 2015 13:13:40 GMT

Hi Gerry, unfortunately, there is only a date stamp.

Re: Claculating net working days

jpenuliar — Mon, 13 Apr 2015 13:15:27 GMT

Is that 75 Statuses on Write-off tab?

yeah that seems a lot, and probably no way to tell which performances your user would want to look at.

You can probably do Ad-hoc style Straight Table, with 2 listboxes(Always one selected Value) to select Status 1 and Status 2, which triggers to show in your Chart to 2 expressions showing the corresponding Dates and the third expression to Networkdays().

Obviously, this will only show 1 active performance metric, if your Customer wants to see more, that's another problem.

Re: Claculating net working days

Mon, 13 Apr 2015 13:19:15 GMT

Hi Gerald,

can you not just limit the initial load then to just those statuses? And then concatenate the remainder at the end if you still need to see them

Joe

Re: Claculating net working days

robert_mika — Mon, 13 Apr 2015 14:16:27 GMT

This is not fully workable solution but from what I have read you need to choose status from two fields to get the difference.

In attached you have draft of what I think this could look like:

Two sites, two states.

Left with "from" the right with "To" and in the middle the result.

You need to pick up the Claim number and status for both of the parts to get the result in "Difference in days box".

I can not make to work one ClaimNo field to affect both states and as well you need to know which status is previous to another.
Maybe someone else could pick this up to develop full workable solution