Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table with data based on the employee's work date (WorkYear, WorkMonth). If there are employees who separated, I need to count them by their TermDate, but the Year and Month of the TermDate should equal the WorkYear and WorkMonth to get an accurate count by WorkYear and WorkMonth. I tried the following set expression but it doesn't work. Any suggestions?
aggr(sum({<WorkYear={"=Year(TermDate)"},WorkMonth={"=Month(TermDate)"}>}Separated),WorkYear,WorkMonth,HomeBusUnit)
Maybe this.
=Sum(Aggr(If(WorkYear = Year(TermDate) and WorkMonth = Month(TermDate), Sum(Separated)), WorkYear, WorkMonth, HomeBusUnit))
sum(aggr(sum({<WorkYear={"=Year(TermDate)"},WorkMonth={"=Month(TermDate)"}>}Separated),WorkYear,WorkMonth,HomeBusUnit))
That almost gets the job done, as I am seeing some numbers in the column now, but they don't reconcile with another table that shows #Separated Employees by their EmpID and TermDate.
Hi Anat. Your expression is returning 0 in the column so possibly some progress was made since originally my expression just returned a dash/null.
I decided to take a different approach and redesigned my data load script. The data for the separated employees is now being concatenated to the main table by EmpID, Year, Month, and Business Unit, which allows for simpler calculations that work correctly. Thanks for your suggestions...although they were possibly on the right track I think fixing the data load made more sense. Thanks again!