Forum Discussion
Column Values based on Rank and Date
Hi,
I have the table below which uses RANKX to show the occurrence of each employee for each day.
| Employee | Date | Start Time | End Time | Code | RANKX (Occurrence) |
| 1 | 1/10/22 | 07:00 | 19:00 | Working | 1 |
| 2 | 1/10/22 | 07:00 | 13:00 | Working | 1 |
| 2 | 1/10/22 | 13:00 | 19:00 | Holiday | 2 |
| 3 | 2/10/22 | 07:00 | 19:00 | Working | 1 |
| 4 | 2/10/22 | 07:00 | 13:00 | Working | 1 |
| 4 | 2/10/22 | 13:00 | 19:00 | Holiday | 2 |
| 5 | 2/10/22 | 07:00 | 19:00 | Working | 1 |
I am stuck in trying to get a calculated column (Status) to show the 'Code' from both the first and second occurence like below.
| Employee | Date | Start Time | End Time | Code | RANKX (Occurrence) | Status |
| 1 | 1/10/22 | 07:00 | 19:00 | Working | 1 | Working |
| 2 | 1/10/22 | 07:00 | 13:00 | Working | 1 | |
| 2 | 1/10/22 | 13:00 | 19:00 | Holiday | 2 | Working + Holiday |
| 3 | 2/10/22 | 07:00 | 19:00 | Working | 1 | Working |
| 4 | 2/10/22 | 07:00 | 13:00 | Working | 1 | |
| 4 | 2/10/22 | 13:00 | 19:00 | Holiday | 2 | Working + Holiday |
| 5 | 2/10/22 | 07:00 | 19:00 | Working | 1 | Working |
Any pointers would be appreciated.
Hi,
Try this calculated column formula
Status = if(Data[Employee rows]=2,if(Data[Rank]=1,BLANK(),CONCATENATEX(filter(Data,Data[Employee]=EARLIER(Data[Employee])&&Data[Date]=EARLIER(Data[Date])),Data[Code],"+")),Data[Code])Hope this helps.
9 Replies
Hi,
Write these calculated column formulas
Employee rows = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Employee]=EARLIER(Data[Employee])))Status = if(Data[Employee rows]=2,if(Data[Rank]=1,BLANK(),CONCATENATEX(filter(VALUES(Data[Code]),Data[Employee]=EARLIER(Data[Employee])),Data[Code],"+")),Data[Code])Hope this helps.
Thank you Ashish,
I amended the "Employee rows" so it counts the rows for each Employee for each date.
Employee rows = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Employee]=EARLIER(Data[Employee])&&'Data'[Date]=EARLIER(Data[Date])))Expanding my data (1/9/22-31/12/22), "Status" is now including the value of "Code" from other dates.
I've tried amending "Status" to
Status = if(Data[Employee rows]=2,if(Data[Rank]=1,BLANK(),CONCATENATEX(filter(VALUES(Data[Code]),Data[Employee]=EARLIER(Data[Employee])&&Data[Date]=EARLIER(Data[Date])),Data[Code],"+")),Data[Code])but the result is the same.
Hi,
Now that you have added date in the Employee rows calculated column formula, your requirement is different from the one you had earlier posted. Please explain the revised question in simple Engligh and show the expected result. Also, share some data to work with.
Why should the status be blank for the first rows of employees 2 and 4 ? Shouldn't that say Working?
I want to have the "Status" for each Employee for each day.
