topic Last six months' performance in QlikView

Last six months' performance

Tue, 21 Sep 2010 12:48:31 GMT

Hi,

I would like to monitor a technician's performance based on the last 4 months' average repair time.

How would I go about doing this if the date is called 'CloseDate', the repair time is called 'RepairDuration' and the technician's name is 'John Smith'?

In my script, 'CloseDate' becomes 'MonthYear':

Monthname(CloseDate) as MonthYear

The expression in my line chart is currently like this:

avg ({AssignedUser = {'John Smith'}, MonthYear = {'Jun 2010', 'Jul 2010', 'Aug 2010', 'Sep 2010'}>} RepairDuration)

This works and produces what I want it to but I would like it to be dynamic and adapt accordingly without me having to change the expression each time.

Thanks,

Last six months' performance

jonathandienst — Tue, 21 Sep 2010 12:54:55 GMT

Normally you would create list boxes for AssignedUser and MonthYear. Now the expression avg(RepairDuration) would show the data according to the selections of AssignedUser and MonthYear.

Or have I misunderstood your requirements?

Jonathan

Last six months' performance

jonathandienst — Tue, 21 Sep 2010 12:59:06 GMT

On rereading your post I think I see what you want.

This expression:

avg (CloseDate= {">=$(=MonthStart(Today(), -4))"}>} RepairDuration)

will return the value for the selected user (from a list box or chart dimension) far the last 4 months.

Is that it?

Jonathan

Last six months' performance

Tue, 21 Sep 2010 13:03:30 GMT

Jonathan,

I'll try your solution. The thing is that I want it to display automatically without listbox interference.

Thanks

Last six months' performance

jonathandienst — Tue, 21 Sep 2010 13:09:12 GMT

But on what basis are you selecting the AssignedUser?

Jonathan

Last six months' performance

Tue, 21 Sep 2010 13:24:40 GMT

I've altered your expression slightly but it works. I've added the AssignedUser in and it will remain on that user. I will then implement multiple charts; one for each AssignedUser.

The modified expression is:

avg({MonthYear={">$(=MonthStart(Today(),-4))"},AssignedUser = {'John Smith'}>}RepairDuration)

Could you explain to me what the following portion of the expression means:

Thanks,

Last six months' performance

jonathandienst — Tue, 21 Sep 2010 16:13:44 GMT

> greater than. Could be replaced with >=

$(= start a computed macro expression - up to the closing bracket

The computed macro expression in this case is: MonthStart(Today(),-4)

Jonathan

Last six months' performance

jonathandienst — Tue, 21 Sep 2010 16:20:48 GMT

Regarding AssignedUser:

Are you sure that that is the best approach? It seems to me that you will be creating a number of essentially identical sheets/charts to display stats for the different AssignedUsers. Unless you have some compelling reason for that approach, I would recommend that you have a single sheet/chart with the Assigned User selected from a list.

You can still title the graph with the selected assigned user and if necessary, only display the chart if exactly one user is selected.

The fixed charts approach that you are proposing would mean that if a new assigned user is added to the data source, you will need to modify the model to add a new chart.

Just my 2c worth

Jonathan

Last six months' performance

Wed, 22 Sep 2010 07:08:48 GMT

Hi Jonathan,

The objects will all be for a 3rd party to view only so I don't want to make them based on selection.

You have a valid point on using selection to generate the associated data but I think I'll go with the multiple charts for this one.

Thanks so much for your help, I really appreciate your time.