Dimension values become null

[SergioS]

I need to analyse for each employee his working time hours on week basis.

Every week has a different number of working days.
These data come from Calendar Table with four dimensions: Week - Week Start Date - Week End Date - Working Days.
Employee birthdays are holidays, so the Due Working Days, on the birthday week are decreased by 1.

Time entries are recorded by employees in Harvest.

The outcome must be a Straight Table as per attached screenshot.

Column # - Meaning
1 to 4 - dimensions from Calendar Table
5 - Birthday detects if the employee birthdays falls into that week
6 - Due Weekly Working Days = [Working Days] - Column(1)
7 - Due Weekly Working Hours = Column(2) * 8,40 (comma as decimal separator)
8 - Worked Hours = Sum(timeHours) data from Harvest

If there are noTime Sheet data for a specific week the corresponding row is not shown (see picture 1)

Picture 1

If I want data forced to zero the formula in Column #8 must be changed to = Sum(timeHours) + 0 * Sum({1} timeHours)
So I can see all the rows *BUT* the Working Days dimension values are null (see picture 2)

Picture 2

Any clue? Many thanks in advance.

[chrismarlow]

Hi,

It is going to be something in the data model that means those columns are referring to something that does not exist on those dates outside your calendar object (noting they appear to be future dates).

Quite difficult to tell without seeing data from tables for a week that 'works' and a week that does not . If you can attach an extract of both calendar and fact (with any personal data removed) it might be more obvious. Using Column in your formulea also makes it a bit hard to follow ... you could be more explicit there.

I would observe that your Birthday does not seem to work either - i.e. in row dated 19/10 would column Due Weekly Working Days not be 4, or have I misunderstood?

Cheers,

Chris.

[SergioS]

Hi Chris, first of all thanks for your reply.

Your analysis is correct (see the Data Model picture): for a specific week  of the [Calendar] table the corresponding week for a specific user in [Users Time] table *has no* entries for "timeHours" field.

One thing about Birthday, the formula was incomplete so no subtracting "Column(1)", fixed now.

Could you please advise about how to extract the data you need? Thank you.

Best regards, s

[chrismarlow · Accepted solution]

The solution here was to add some dummy rows for missing Weeks in Users Time table from Calendar.