The simple phrase is "Total of Sales from records when the last action on a account resulted in a status of "complete"
I'm working in textboxes and I'd like to get a total based on two conditions,
1) The record has the max(time) within a group (just that record, not the sum of the entire group)
2) A field in that record equals some text
| Record# | Account | Update DateTime | Status | Sales |
|---|
| 1 | 123456 | 1/1/14 1 pm | Assigned | 1 |
| 2 | 123456 | 1/2/14 2 pm | Complete | 2 |
| 3 | 987654 | 1/2/14 1 pm | Complete | 4 |
| 4 | 987654 | 1/2/14 2 pm | Complete | 8 |
| 5 | 987654 | 1/3/14 1 pm | Complete | 16 |
| 6 | 456987 | 1/2/14 1 pm | Complete | 32 |
| 7 | 456987 | 1/3/14 2 pm | Assigned | 64 |
Here are two examples and my expected output
1) No filters, Total for "Complete" is 18, "Assigned" is 64
- Records 2, 5, and 7 are considered because they are the last time within their respective account. One text box will sum up Complete (2+16), the other Assigned (64)
2) The only date selected is 1/2/14, Total for "Complete" is 42, "Assigned" is 0
- The filter for date (1/2/14) removes all but 2, 3, 4, and 6; The filter for last account times within those leaves 2, 4 and 6. (Since I want only one record per account, I take the record with the max time [that falls within the date filter] in account 987654) One text box will sum up Complete (2+8+32), the other Assigned (0)
I've been trying all sort of things with FirstSelectedValue(), Aggr(), set expressions and some if() statements.
=Sum(
Aggr(
FirstSortedValue({$<
[Status]={'Complete'}
>}
[Sales],
-[Update DateTime]
)
,[Account]
)
)
The problem with this is filtering on status gives strange results.
