Problem Statement: Below is my data
The table has two data of Project A, one generated on 11/10 and another on 11/09.
I want to create a master table which will give me few things like “Distinct Project Name, Last_Execution_DateTime, Overall Status”
- Distinct Project Name means it should give only Project A in first row, if Project B in second Row
- Last_Execution_DateTime Example Project A ran on 11/10, the most last execution datetimewas 11/10/2023 21:20:00
- Overall status means if Status of Project A executed on 11/10 has any one FAIL status, overall status should be FAIL otherwise PASS.
Expected Result should be:
| Project Name |
Last Execution Date |
Overall Status |
| Project A |
11-10-2023 21:20:00 |
FAIL |
Hi @mohit.itsector,
To get the last execution date, you just have to create a calculated field using the max function:
max(Last_Execution_Date_Time)
Next, count the number of FAIL statuses per project.
FAIL count = countOver(ifelse(Status = ‘FAIL’, Status, null), [Project_Name], PRE_AGG)
Finally get the overall status:
Overall Status = ifelse({FAIL count} > 0, ‘FAIL’, ‘PASS’)
2 Likes
I have added few more projects with multiple dates, I am also getting right “failed” count, as well as overall status , however, I am getting OverAll_Status for all the dates which I am not suppose too. I am suppose to get only for Last_Execution_Date Only.
Please Note: I am using “table” and attach is the data I am using for dataset
Continue…
Please find my data below
Hi @mohit.itsector,
Can you use maxOver instead of max to calculate Last_Execution_Date_Time?
maxOver({Last_Execution_Date}, [Project_Name], PRE_AGG)
Create a field like this:
Is Last Execution Date = ifelse(Last_Execution_Date = Last_Execution_Date_Time, ‘Yes’, ‘No’)
Then filter your visual by:
Is Last Execution Date = Yes
You are awesome. Its solved my issue.
1 Like
