topic Re: Re: Count state from last occurence in QlikView

Count state from last occurence

Thu, 20 Feb 2014 11:49:56 GMT

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 😉

Re: Count state from last occurence

nagaiank — Thu, 20 Feb 2014 12:08:27 GMT

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;

Re: Count state from last occurence

Thu, 20 Feb 2014 13:16:03 GMT

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

Re: Count state from last occurence

Fri, 21 Feb 2014 09:06:17 GMT

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:

Re: Re: Count state from last occurence

Fri, 21 Feb 2014 09:57:52 GMT

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

Re: Count state from last occurence

Fri, 21 Feb 2014 12:03:00 GMT

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 😉