
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Count state from last occurence
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 😉
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 😉
