I have a problem where I want to tally Uploads based on a Status selected. I want to find the latest Date where the Uploads column has a value and then get that sum for all Customers. I am able to accomplish that using the following:
sum(aggr(FirstSortedValue({1<Uploads={*}>}Uploads,-timestamp(Date, 'YYYY/M/DD hh:mm:ss')),hco_id))
I have a table of data as follows:
| Customer | Date | Status | Uploads |
| 1 | 1/1/19 | Registered | 0 |
| 1 | 2/1/19 | Uploaded | 1 |
| 1 | 3/1/19 | Submitted | - |
| 2 | 1/2/19 | Registered | 0 |
| 2 | 1/3/19 | Uploaded | 2 |
| 3 | 2/4/19 | Registered | 0 |
| 3 | 2/5/19 | Uploaded | 3 |
| 3 | 2/6/19 | Submitted | - |
| 3 | 2/7/19 | Withdrawn | - |
The issue is I don't want a Customer to be included if their latest status doesn't match with what I have selected.
When I select a value of 'Submitted', then I get a correct sum of 4. The issue is when I select 'Uploaded'. I get an incorrect sum of 6. I don't want to include Customer 1 or Customer 3 because their most recent status is Submitted. I should only include Customer 2 with an Upload value of 2.
Does anyone know how to get my list of customers where latest status is Uploaded and then find the latest Upload count for those customers? I need to exclude the customers that are already in the 'Submitted' status.
I'm able to do this in a table, but getting this to work in a KPI has proven to be a different problem. Additionally, for the time being, I'm not able to change the way my data is structured.
Any ideas on how to accomplish this?
