How to get the first value in a date range

[#1]

Hi,

I have the following table:

ID   date                     State   Index

1    01/01/2011               3     1

1    01/01/2011               4     2

2    01/01/2011               1     1

2    02/01/2011               2     2

1    02/01/2011               2     3

1    02/01/2011               4     4

2    02/01/2011               4     3

3    02/01/2011               4     1

I want to count the IDs  which receive state 4 for FIRST time within a date, eg for  02/01/2011 the Count would be 2:

ID   date                     State

1    02/01/2011               4   <--- not first date in which ID 1 has state 4

2    02/01/2011               4

3    02/01/2011               4

Index is the order inwhich the id changes from state

I have something like this, which is not working as expected

=

Count

(

  {$<

   StateID={4},

    ContactID={"=min({$<State={4} Index) = min({$<date=varCurrentDate  ,State={4} Index)"}

  >}

  ID

)

Any help is appreciated, thanks!

[markodonovan]

Hi Paola,

Is this what you are after:

Thanks

Mark

http://practical-qlik.com

https://twitter.com/PracticalQlik

http://practical-qlikview.tumblr.com/

[#3]

Thanks Mark!