I have a calculation in my load script that Stephan helped me with, thank you again Stephan. I have been layering in another calculation on top of the original FTE calculation in my UI, but it makes my UI very slow. I would like to move the calculation to the load script, but I am having trouble getting the calculation to work.
I basically want to divide the count of worker cancellations by FTE to give me a ratio of worker cancellations to workers and then do the same for sites.
Original Calculation in Load Script
FTECalculations:
Load
UID,
FTEMonth,
DaysWorked/DaysInMonth as FTE;
MonthName(OADate) as FTEMonth,
Count(DISTINCT OADate) as DaysWorked,
Only(Day(MonthEnd(OADate))) as DaysInMonth
From OAMaster.qvd (qvd)
Group by UID, MonthName(OADate);
Additional Field for Calculation
left Join (TOACalculations)
Load
UID,
TypeDescription,
if(CancelledByWorkerDate<> Null() and TypeDescription='Cancellation',CancelledByWorkerDate) as WorkerCancellationDate,
if(CancelledByWorkerDate<> Null() and TypeDescription='Cancellation',1,0) as WorkerCancellationFlag,
if(CancelledBySiteDate<> Null() and TypeDescription='Cancellation',CancelledBySiteDate) as SiteCancellationDate,
if(CancelledBySiteDate<> Null() and TypeDescription='Cancellation',1,0) as SiteCancellationFlag
I ended up bringing in the FTEMonth into a canonical calendar. The issue that I am having now is that the FTE calculation is not correct if I use month year dimension from the canonical calendar, but correct if I use the FTEMonth dimension from the FTECalculations table.
