Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
mikegrattan
Creator III
Creator III

Set expression to compare the year and month of one date to the year and month of another date

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)

mikegrattan_0-1695926393622.png

 

 

Labels (1)
5 Replies
BrunPierre
Partner - Master
Partner - Master

Maybe this.

=Sum(Aggr(If(WorkYear = Year(TermDate) and WorkMonth = Month(TermDate), Sum(Separated)), WorkYear, WorkMonth, HomeBusUnit))

anat
Master
Master

sum(aggr(sum({<WorkYear={"=Year(TermDate)"},WorkMonth={"=Month(TermDate)"}>}Separated),WorkYear,WorkMonth,HomeBusUnit))

mikegrattan
Creator III
Creator III
Author

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.  

mikegrattan
Creator III
Creator III
Author

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.  

mikegrattan
Creator III
Creator III
Author

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!