You may use the FirstSortedValue() function to get the last state and then count the state.
LOAD * Inline [
project_id, date, state
111, 1.1.2014, A
111, 5.1.2014, B
222, 2.1.2014, C
222, 3.2.2014, B
333, 3.1.2014, C
LOAD project_id, FirstSortedValue(state,-date(Date#(date,'D.M.YYYY'))) as Last_state
Group By project_id;
Well...I'm still not sure, what is wrong, so here is example I made from some data. I described problem in text object in this file:
QVtest_global.qvw 156.2 K
i'd missed an extra dimension in the aggr second option.
the result should be
=-sum(aggr(max(total <project> date)= date,project,state,date))
You say in your example that result should be fully = 3 but partially = 1
however all the projects seem to be complete? Project 144670's status on the latest date is complete.
I've used a firstsortedvalue() function in the example to return the latest state for each project.
Let me know what you think,
QVtest_global.qvw 164.0 K