topic Assistance required on loop! in QlikView

Assistance required on loop!

Fri, 20 Jan 2012 16:22:55 GMT

Good afternoon everyone.

I've been struggling with the idea of what I am trying to acheive for some time.

I'm producing some resource capacity dashbords based on our data from Clarity PPM

In this system, there are projects with 1 or more tasks.

each task will have one or more resources assigned.

That resource then books time (ACTUALS) to the assignment (task) and also has forward looking ETC's on that project.

Aside from the project/task/assignment each resource has a general availability.

ACTUALS, ETCs and Availability are all time-sliced into the same tables

In my qvw, you can see the script has a loop occuring.

I'm reading in the time-sliced data twice. once with actuals/ETCs against time and once with resource availability.

I need them governed by a single data picker, so I can see on a particular date, how many ETCs a resource has (thats the planned schedule) and what their availability is, so a simple sum will show their spare capacity.

I'm not sure how to construct the query so that I can acheive this.

I need the two time slice areas separate from each other, but linked by date.

Thanks in advance for any assistance you might be able to offer.

Assistance required on loop!

Mon, 23 Jan 2012 08:54:28 GMT

No one? :-S

I'm sure this is such a simple task - and i'm probably not seeing it as it is that simple...

1 date range affecting two separate dimensions..

but not joining the dimensions together..

Assistance required on loop!

nstefaniuk — Mon, 23 Jan 2012 10:33:23 GMT

Hi. 3 solutions:

1) create 2 differents facts concatenated, each with a field of type date. Selecting a date will select the first fact with the first measure, and the second with the second measure.

2) create a matrix that link each fact to 2 dates, the date of first dimension and the date of second dimension. So if you select a date, it will select all lines of fact where the date is in first dimension OR in second dimension.

3) in the chart, use the date in Set analysis, with dimension1 = {"Date"} for the first measure and dimension2 = {"Date"} for the second measure.

For me the best solution is the 1.

Assistance required on loop!

Mon, 23 Jan 2012 10:59:19 GMT

Not quite sure I have understood correctly, but how about concatenating the 'AVAILABILITY' and 'ACTS_ETCS' tables together (forced using CONCATENATE LOAD)?

You could use a flag to differentiate bewteen the two record types in the table so you can work with one set or the other in expressions etc.

Regards,

Gordon

Assistance required on loop!

stephencredmond — Mon, 23 Jan 2012 12:57:52 GMT

I agree with Gordon.

I might be tempted to join the ACTS_ETCS table with the Task_Assignment table first and then concatenate the AVAILABILITY table. This would null out any association between Project and Availability but, this would appear to be logically correct.

Regards,

Stephen

Assistance required on loop!

Tue, 24 Jan 2012 12:36:48 GMT

Thanks for your replies, guys.

Gordon,

the trouble with concatenating is that the facts link to two different attriobutes on the TaskAssignment table.

ACTS_ETCS link on ASSIGNMENT_ID where AVAILABILITY links on RESOURCE_ID.

I will try your suggestion Stephen, and nstefaniuk's suggestion and get back to you all..

Assistance required on loop!

Tue, 24 Jan 2012 15:31:01 GMT

Hmm, I'm struggling here...

Have joined (left join) on the ACTS_ETCS (Actuals and ETCs)

then concatenated the availability - so they share common SLICE_DATE and HOURS values..

Doesnt behave how I need..

The end result i'm looking for is to be able to show a graph of a SUM of AVAILABILITY on any particular week or month, and then show the SUM of ETC i the same graph so I can see the available capacity (availability-ETC)

Likewise, if I choose a project, I would like to see if the ETC of the project for any particular month is greater than the total available time of the resource.

This means I would definitely need to link the availability (key field is resource id) to the task_assignments table - but with a single date value linking them...

somehow..

going around in circles here (much like the circular reference in my original attached qvw lol)

Assistance required on loop!

stephencredmond — Tue, 24 Jan 2012 15:44:46 GMT

Hi,

I did this:

Fact:

LOAD ASSIGNMENTID,

SLICE_TYPE,

SLICE_DATE,

MONTH_END,

HOURS as Actual_Hours

FROM

ACTS_ETCS.qvd

(qvd);

Join

LOAD PRSTATUS,

PROJECTID,

TASK_STATUS,

ASSIGNMENTID,

RESOURCE_ID

FROM

Task_Assignment.qvd

(qvd);

Concatenate

LOAD RESOURCE_ID,

SLICE_DATE,

MONTH_END,

HOURS as Available_Hours

FROM

AVAILABILITY.qvd

(qvd);

LOAD PROJECT_NAME,

PROJECTID

FROM

PROJECTS.qvd

(qvd);

Was able to analyse the Actual hours versus Available.

Stephen

Assistance required on loop!

Wed, 25 Jan 2012 11:45:57 GMT

Thanks a lot Stephen,

I have run this into my script and, yes I can compare the both together, but the moment I select a project for example, it obviously filters out the availability.

I was wondering if there is a way I can do set analysis of some description so that if I select a PROJECTID,

my graph (bar chart) sums up the availability disregarding that selection.

I would want the ACTUALS to behave 'normally', but availability to show without the selection on project.

Does this make sense?

The same goes for selection by resource - I would want to be able to pick a single resource, and see their total ACTUALS by time, and their availability.

Regards,

Matt

Assistance required on loop!

stephencredmond — Wed, 25 Jan 2012 11:54:08 GMT

Hi,

Yeah, I mentioned that further up - project selection will exclude the availability and that is logical because selecting a project will not have an association with the availability.

However, you can insert a Set in the calculation of the availability that will ignore selection on ProjectID (and any other Project fields that might be on the screen) like this:

Sum ( { < ProjectID=, ProjectName=, [Project Manager]= > } Available_Hours )

(I have added in a few potential fields that you might have - adjust to what you actually have.

Regards,

Stephen

Assistance required on loop!

Wed, 25 Jan 2012 12:05:28 GMT

Many thanks Stephen.

It has highlighted a slight issue in my data also (which can only be a good thing)

Great help. I appreciate it.