topic Re: Personell fluctuation week_over_week in QlikView

Personell fluctuation week_over_week

datanibbler — Fri, 22 Nov 2013 10:59:16 GMT

Hi,

I have a chart showing personell_fluctuation per week:

It calculates as

"nr_of_employees_who_left_in_given_week / nr_of_employees_total_in_week_before".

The expression I currently use to calculate the second part is

>>> COUNT({$<Woche = {$(=WEEK(min(%Datum) - 1))}>} DISTINCT Pers_Nr_g) <<<

That works as such - but strangely, when I select 2013 and Nov, then I see five weeks - 44, 45, 46, 47, 48 - but that figure is displayed only for week 44.

Can somebody help me there, please?

Thanks a lot!

Best regards,

DataNibbler

Re: Personell fluctuation week_over_week

datanibbler — Fri, 22 Nov 2013 11:17:07 GMT

Ah - yes, I see - there's only one >>> min(%Datum) <<< when no specific week is selected...

How to do that, then?

Re: Personell fluctuation week_over_week

datanibbler — Fri, 22 Nov 2013 12:41:10 GMT

Hi,

I realized that the field >Woche< which I use as dimension is a numeric field anyway - so I can simply calculate (Woche - 1) - but that doesn't work either.

I'm really stuck on this one.

Can anybody help me here?

Thanks a lot!

Best regards,

DataNibbler

Re: Personell fluctuation week_over_week

datanibbler — Fri, 22 Nov 2013 14:43:46 GMT

What I don't get about this is, above all:

It works FINE in a straight_table_diagram - I can have

- My_week as dimension

- COUNT(DISTINCT Pers_Nr5) as expression for the current week's value

- (min(%Datum)-5) as expression for a date in the past week

- week(min(%Datum)-5) as expression for the past week

<=> The expression I use for last week's value

COUNT({$<Woche = {$(=week(min(%Datum)-5))}>} DISTINCT Pers_Nr5)

does not work. I get only 0s.

Can anybody help me here?

(min(%Datum)-5)

Re: Personell fluctuation week_over_week

whiteline — Fri, 22 Nov 2013 22:18:28 GMT

Hi.

With set analysis the set is calculated once for the entire chart. So you can't use it to calculate rolling expressions.

Try inter-record functions instead as above/before.

Or make additional calculations in script.

Re: Personell fluctuation week_over_week

datanibbler — Mon, 25 Nov 2013 08:09:36 GMT

Hi whiteline,

thanks a lot! I thought of that in the meantime - and I tried using the aggr() function.

I haven't made it yet, but it seems I can use that function to get there.

Best regards,

DataNibbler

Re: Personell fluctuation week_over_week

datanibbler — Mon, 25 Nov 2013 08:34:40 GMT

Hi,

I guess it has to be more complicated still. One aggr() fct does not seem to be enough to make it in the diagram - I can have the nr_of_the_past_week displayed for every week using aggr()

<=> I need the COUNT_of_employees_in_the_past_week, so for all I know I would need SET_analysis - but I cannot see how I could do that.

I will try creating the aggregated COUNT per week in the script instead so that the diagram can use it as a native field.

Any help with this would be appreciated.

My base formula (for "this week's count") is

>>> aggr(COUNT({$<%Datum = {"$(= '<' & DATE(v_today))"}>}DISTINCT Pers_Nr5), Woche) <<<

That seems to be too complicated, though.

Thanks a lot!

Best regards,

DataNibbler

P.S.: OK, I think I know how - it's just going to take a while: I need a LOOP in the script through all 53 weeks of the year. The remaining difficulty is this:

- Just counting the employees would, of course, be no good as we have one record per emplwouloyee per day in that table - everyone would be counted 7 times...

- Counting with a DISTINCT would not work either as the same emps (= same IDs) work for us every day - but of course the nr. fluctuates.

- In other scenarios, I have, on the GUI, used the expression >>> Pers_Nr&Wochentag <<< - but I cannot use that since the field "Wochentag" is in another table, the master_calendar.

=> I would need to manually build that field in that table, then I can again concatenate the Pers_ID and the day_of_week and divide that by 7 and I'll have an avg nr.

=> Can I do that simpler in some way?

Re: Personell fluctuation week_over_week

whiteline — Tue, 26 Nov 2013 21:00:41 GMT

Hi.

You have two kinds of facts in this case both connected with Calendar (week).

First one is the number of employees that works in a given week. << closing entry

Second is the number of employees that left. << turnover

Suppose that you have one record for each employee to be able to link some attributes (position, purpose, department)

You can easily connect them with a shift, especially if the above mentioned report is the only requirement.

Although, you can be restricted in making changes of your data model. Though you'd better post it here, so that we can find a proper solution.