topic Re: sum hours Under terms in QlikView

sum hours Under terms

Wed, 13 Jul 2011 07:34:26 GMT

Hello everyone,

I need some tips around here.

Pls see attach file.

As you can see I have an employee named "roy" who worked in some days within the month "may".

He have Efficiency Index for every shift.

In the 15/05/2011 he had a Training Course.

I need to have a table with one row and three columns:

1-Emp' name

2- Efficiency in 4 shifts before the Training Course.(avg)

3- Efficiency in 4 shifts after the Training Course.(avg)

Thanx a lot guys!!

sum hours Under terms

Wed, 13 Jul 2011 10:33:08 GMT

someone?

sum hours Under terms

swuehl — Wed, 13 Jul 2011 10:54:26 GMT

Hi avivcoil,

I think you could use a set expression to limit the dates, like

=avg({$<date ={"<=$(V_Train)"}>} eff)

for the dates before and including the training day

and

=avg({$<date ={">$(V_Train)"}>} eff)

for the period after the training day (both expression as expression in a chart straight table, dimension = name).

I had some difficulties applying to your example, please assure that the "date" is really a date format (I think right now it is a text) before comparing to the variable (and this variable should also be set as date using makedate).

Hope this helps,

Stefan

sum hours Under terms

Wed, 13 Jul 2011 11:48:31 GMT

Hi swuehl,

Great!

Thats what I was looking for.

You are right,I also have a problem with the the dates.

Now I may need something even more difficult...

I still need the same table - i.e. avg efficiency, but now calculate it a bit diffrent.

The calculation methed will be:

a-before the training,go back and calc' for the last 30 (before the training) hours whats was the Eff.

b-after the training,calc' the next 30 hours (after the training) whats was the Eff.

do you still think its possiable....?

Thanx again

Re: sum hours Under terms

swuehl — Wed, 13 Jul 2011 12:48:36 GMT

Hi avivcoil,

hmmmm, yes

At least I tried in attached sample.

This is my approach:

I used a accumulated hour to have something like a consecutive timeline for orientation (like date).

I set three variables = cumHour at the time of traing, 30 h before and 30 h after (It was again not quite clear how to handle the day of training, because you got also hours for that day, count them to before or after training?)

Then I used set expression again to get only the eff values within the desired hour range (But with some jitter how to handle the dates hours at the border of these ranges).

Maybe some room for improvement, but my idea should be clear.

Regards,

Stefan

Re: sum hours Under terms

swuehl — Wed, 13 Jul 2011 13:43:15 GMT

Hi avivcoil,

two more ideas,

if you want the expression to be not influenced by selections (which I assume), you should change the set expression to e.g.

=avg({1<cumHour ={"<=$(H_Train) >$(H_first)"}>} Eff)

Second, I noticed that the hours are fluctuating from date to date. It might be an idea to use a weighted average for efficiency or to use the original calculation for efficiency (which is probably something per time slice) instead.

Hope this helps,

Stefan

Re: sum hours Under terms

Thu, 14 Jul 2011 06:22:28 GMT

GREAT !

Really nice, I have learned some tricks here!

thanx