Expression in a pivot table help

[m4u]

Hello

I have two tables:

employee timesheet with employee, role, date , date year, date month and hours worked on that date

standard hours - date and standard hours for that date

I want to show both figures in a pivot table pivoted by role, employee, year, month - whith each cell showing me the amount of hours the employee worked that month and the number of standard hours for that month (basically the standard hours figure is the same for all employees)

I have Sum(HoursWorked) to show the hours worked, and SUM(StandardHours) to show the standard hours

So far so good

The problem begings on those months where a certain employee had no records at all. For some reason, for those months the standard hours figure is showing zero as well, even though it should show the figure from the standard hours table, which exists

What am I doing wrong, or what should I change to make it work?

[hectorgarcia]

I did something to identify hours worked by month to identify employees that did not input hours total or partially in the script

mindate:

load

min(num(Fecha)) as

fechamin

resident

MSP_TimesheetActual;

replace

LET varMinDate = Num(Peek('fechamin', 0, 'mindate'));

LET

varMaxDate = floor( Num(monthend(Today()),0));

//LET varMaxDate = Num(Today());

//Create all fullmpnths in the range from varMinDate to varMaxDate, this is to incude accumulations in months without enrollments

TempCal:

LOAD

$(varMinDate)

+Iterno()-1 AS Num

,

Date

($(varMinDate)+Iterno()-1) AS

TempDate

AUTOGENERATE

1 WHILE $(varMinDate)+Iterno()-1<= $(varMaxDate)

;

//Building the master calendar with most date dimensions

table_aux:

load

distinct date(TempDate, 'YYYY-MM-DD') as

Fechavinc_AUX

resident

TempCal order by TempDate

Asc;

Drop

table

TempCal;

// take all periodo and all AUDAFFILIATIONNUMBER

join

load

distinct

recurso1

as

recurso_AUX

resident

MSP_TimesheetResource

//order by keyusermonth,Fecha

where

not exists(R1, recurso1)

;

auxtable

:

load

Fechavinc_AUX

AS creationdate

,

recurso_AUX

AS

recurso

resident

table_aux

order

by recurso_AUX ,

Fechavinc_AUX;

left

join

load

recurso

,

creationdate

,

sum

(Horas) as

cantidad

resident

timesheet1

where

flagappr=1

group

by recurso , creationdate

order

by recurso , creationdate

;

//store auxtable into auxtable.qvd;

//drop table table_aux;

horasporusermes:

load

distinct

recurso

,

left

(creationdate,10) as Fecha

,

if

(isnull(cantidad) or cantidad<1,0,cantidad) AS Cantidademp

,

//numsum (if ( AUDAFFILIATIONNUMBER = PEEK(AUDAFFILIATIONNUMBER) , rangesum ( cantidad , peek (acumactiv1)) , cantidad)) as acumactiv1

rangesum (if ( recurso = previous(recurso) and monthname(creationdate)=previous(monthname(creationdate)) , rangesum ( cantidad , peek (acumactiv1)) , cantidad)) as

acumactiv1

resident

auxtable

order

by recurso , creationdate

;

drop

table

auxtable;

drop

table

table_aux;