Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Community 🙂
I need to count projects depending on theirs states, but problem is, that state of projects is changing over time and I want to count always last occurence. I tried this, but it doesn't work for me: count(aggr(max(rep_date),project_id))
Data look like that:
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
And result I want is:
count:
B = 2
C = 1
(state A from project 111 and state C from project 222 are not last occurence...)
Could you help me please?
Thank you 😉
Hi Miroslav
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,
Erica
You may use the FirstSortedValue() function to get the last state and then count the state.
Table1:
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
];
Table2:
LOAD project_id, FirstSortedValue(state,-date(Date#(date,'D.M.YYYY'))) as Last_state
Resident Table1
Group By project_id;
Hi Miroslav
In the application (not script) try
=-sum(aggr(max(total <project_id> date)=date,project_id,state))
You were nearly there with the aggr, but what you need to do is include a total in the aggr to return the max(date) for each project ID
Let me know if this works for you,
Regards,
Erica
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:
Hi Miroslav
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,
Erica
Hi, THANK YOU, your solution works exactelly like I wanted to 🙂
And you're right, all projects are finally fully completed, my mistake.
Thanks again and wish you great weekend 😉