Qlik Community

Ask a Question

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Specialist
Specialist

Set Analysis Syntax

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.

1-28-2016 9-16-36 AM.jpg

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:

1-28-2016 9-14-50 AM.jpg

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!

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

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

View solution in original post

18 Replies
Creator III
Creator III

Hi,

I think this due to date field

Specialist III
Specialist III

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

Capture.PNG

Hope this helps

Specialist
Specialist

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

Specialist III
Specialist III

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

Thanks

Specialist
Specialist

Sorry, 63, rather.

1-28-2016 9-16-36 AM.jpg

Specialist III
Specialist III

Ok. Let me know if the below helps:

Capture.PNG

Specialist
Specialist

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.

Specialist III
Specialist III

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

Specialist
Specialist

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:

1-29-2016 9-13-50 AM.jpg

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:

1-29-2016 9-19-49 AM.jpg