topic Re: Set Analysis Syntax in QlikView

Set Analysis Syntax

cbaqir — Thu, 28 Jan 2016 16:22:07 GMT

Hi everyone... I am trying to determine the correct Set Analysis syntax for the following situation:

My application has two views - one that looks at the sum(WORKFLOW_DURATION) based on the CUR_WORKFLOW_STEP_NAME and one based on the historical WORKFLOW_STEP_NAME.

In my example, if you select REQUEST_ID 181997, you can see that the historical WORKFLOW_STEP_NAME chart looks correct.

However, the CUR_WORKFLOW_STEP_NAME chart shows the Current Workflow repeating because that's the phase it is currently in but it still sums up the Business Days Workflow Duration for each of the other steps.

I want to see only one line that sums up Business Days Workflow Duration where the CUR_WORKFLOW_STEP_NAME=WORKFLOW_STEP_NAME instead of this:

t's possible that a workflow step could repeat. In the case where CUR_WORKFLOW_STEP_NAME=WORKFLOW_STEP_NAME, I would want to seem the sum of the aggregate workflow duration.

A null Completion date also means a step is current.

Thanks!

Re: Set Analysis Syntax

santharubban — Thu, 28 Jan 2016 16:49:41 GMT

Hi,

I think this due to date field

Re: Set Analysis Syntax

sinanozdemir — Thu, 28 Jan 2016 17:16:14 GMT

Or you can use Aggr() and aggregate the sum by request ID so that eligible and completion dates are collapsed:

Hope this helps

Re: Set Analysis Syntax

cbaqir — Thu, 28 Jan 2016 17:44:19 GMT

but the total for Planning should not be 123. It should be 64.

Re: Set Analysis Syntax

sinanozdemir — Thu, 28 Jan 2016 18:25:21 GMT

Why 64? Can you explain the logic a little bit?

Thanks

Re: Set Analysis Syntax

cbaqir — Thu, 28 Jan 2016 18:38:20 GMT

Sorry, 63, rather.

Re: Set Analysis Syntax

sinanozdemir — Thu, 28 Jan 2016 18:48:06 GMT

Ok. Let me know if the below helps:

Re: Set Analysis Syntax

cbaqir — Fri, 29 Jan 2016 15:47:06 GMT

Not sure what the expression is doing here but it doesn't seem to work on my Historical view when I select 168442, for example.

Re: Set Analysis Syntax

sinanozdemir — Fri, 29 Jan 2016 15:51:39 GMT

I thought you were trying to show the uncompleted lines that didn't have completion dates. If not, can you explain again why you would want to show only 63? I guess I am just not understanding that part.

Thanks

Re: Set Analysis Syntax

cbaqir — Fri, 29 Jan 2016 16:20:54 GMT

Sorry for the confusion... the goal is twofold:

1. For the Current chart, show the aggregated sum of Workflow Duration per REQUEST_ID for the CUR_WORKFLOW_STEP. So, in the case the current workflow step was repeated at some other point, sum them up per request.

2. For the Historical chart, show the aggregated sum of Workflow Duration per REQUEST_ID for each WORKFLOW_STEP_NAME. So, in the case the workflow step was repeated at some other point, sum them up per request.

The dates are the dates each step was eligible for action and where action was completed. A step with a null end date thereby means it is still current.

In the example of 181997, this was correct:

But it doesn't work for 178316. For the Current view, I would expect it to show one row for Step Close Out since 8/20/15 (something like 111 days in my example). For historical, I would expect it to show each step something like these in yellow:

Re: Set Analysis Syntax

rwunderlich — Fri, 29 Jan 2016 16:26:35 GMT

I would add a field in the script:

if(CUR_WORKFLOW_STEP_NAME=WORKFLOW_STEP_NAME, 1, 0) as Flag_CurrentStep

and then in the chart:

sum({<Flag_CurrentStep={1}>}WORKFLOW_DURATION)

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Set Analysis Syntax

cbaqir — Fri, 29 Jan 2016 16:35:57 GMT

Rob,

That works great for the Current chart. Maybe the best solution for the Historical view is to remove the date fields to get the aggregate sum if a step was repeated but is there a way to keep the date fields and still get the right totals?

Re: Set Analysis Syntax

rwunderlich — Fri, 29 Jan 2016 16:40:31 GMT

Do you only want one historical line per Step name? If so which date do you want to show? High, low or both?

-Rob

Re: Set Analysis Syntax

cbaqir — Fri, 29 Jan 2016 16:46:54 GMT

After talking to my customer, I am going to show them on one line but remove the dates from the historical view for now so that makes it easier.

Re: Set Analysis Syntax

cbaqir — Fri, 29 Jan 2016 16:47:25 GMT

Thank you!

Re: Set Analysis Syntax

rwunderlich — Fri, 29 Jan 2016 17:03:43 GMT

Glad you've got it resolved. For the record, I was going to suggest handling the dates with expressions of

min(WORKFLOW_STEP_ELIG_DATE)

max(WORKFLOW_STEP_COMP_DATE)

-Rob

Re: Set Analysis Syntax

cbaqir — Fri, 29 Jan 2016 17:51:59 GMT

The only potential issue here is when the step repeats but it isn't sequential... so:

Step A 1/1/15-1/31/15

Step B 2/1/15-3/31/15

Step A 4/1/15-5/30/15

Would look like:

Step A 1/1/15-5/30/15

Re: Set Analysis Syntax

rwunderlich — Fri, 29 Jan 2016 18:53:55 GMT

An alternative would be to use concat to show multiple dates.

concat(WORKFLOW_STEP_ELIG_DATE, chr(10))

max(WORKFLOW_STEP_COMP_DATE, chr(10))

The chr(10) is a newline so they will only show when hovered over.

-Rob

Re: Set Analysis Syntax

cbaqir — Wed, 03 Feb 2016 23:17:59 GMT

Rob,

I've attached a new sample file with the Flag.

As you can see, it seems to be counting REQUEST_IDs that are not within the Turn Around Time indicated in the class.

For example, I would expect to see 16 REQUEST_IDs in Close Out for more than 120 days, but in reality there only appear to be 6.

How do I get both charts to reflect the correct counts?

Thanks!