Hi,
I have been here before with this and similar issues. Dealing with the personell_data seems to be very tricky, even without considering the fact that it's highly sensitive data.
This time, the issue is about the dimensions "day" and "month":
- In most of our apps, we use a dimension "day" (an expression from a qvs) to display a week from Mon to Sat
- In some we use a variation of that dimension that does not include Sat. Such is the case here.
- The other dimension we have in most cases is the month.
=> Both are based on a master_calendar, also from a qvs.
- In this app, in this chart to display the presence_quota, what I do is basically count the personell_numbers. Easy enough.
- That is correct with the dimension "day" (not considering people being on holiday or being sick or being off-duty) - all those I can
implement using a set_expression, the info is in my table.
<=> On the dimension "month", it is not correct because one personell_number is basically registered once for every day, owing to the
way my table is constructed (it is just enlarged to contain 1rec per day from any employee's start_date to his/her leave_date)
=> A logical solution seems to be dividing that figure by the nr. of working_days in that month - that should be a pretty good
approximation, given the nr. of employees does not differ within that month.
=> I have yesterday developed a code calculating the nr. of working_days per month, using the networkdays() function and a list of
public holidays in the year 2014. I tested it and it seemed good.
<=> Still, the nr. of employees I thus calculate for one month (dimension is "month") is higher than the nr. of employees on one day
within that month.
I have chosen for testing one team where, with the dimension "day", when I select the entire month of August, the nr. of employees in the team is the same every day.
The formula I use here is the following:
>>> sum({$<%Datum = {"$(= '<' & DATE(v_today))"}>}Anz_MA_soll) <<<
(Anz_MA_soll is just a number in an aggregated table, a Count of employees)
=> For the dimension "month", I would use the following formula:
>>> sum({$<%Datum = {"$(= '<' & DATE(v_today))"}>}Anz_MA_soll) / Arbeitstage_im_Monat <<<
(the figures here are higher than the number of emps on any given day, which, again, is the same every day of the month of Aug.)
=> Is there anything in the logic that I have overlooked and that might cause the figures to differ or will I need to put together some sample data?
