Calculated dimension based on max value

[leenlart]

Hello,

I have a table with items, the batch numbers of the items, the state of the batch (CETAT_LOT), the date of the state change (DATEEFFET_LOT). 

The states can be programmed in advance, so for example, a batch of an item can go from  "Open" to the state "For destruction" at a certain date in the product life span. 

I need to create a pivot table showing : the month (DATEJOUR) , items, batch number, and the current state of the batch depending on the month selected.  I will then sum the quantities and value. 

I'm hung up on finding the current state.  Currently to show all states that are before the month selected I have the following :

=if ( DATEEFFET_LOT<= DATEJOUR, CETAT_LOT )

Now I need to take it one step further, and find the max of the states based on the DATEEFFET_LOT<= DATEJOUR. 

I'm a real newbie to this so I can't seem to figure out if I should use aggr, firstsortedvalue, max, or a combination of all of these! 

In my example here, you can see on the 3rd and 4th lines, an example of a batch that has two states that started before July 31st.  I need to take only the last one, so the state = DIS that started the 5th of June. 

If anyone can give me some help with this, you'll be my qlikview hero!

Thanks

[chrismarlow]

Maybe something like this;

=if(max(TOTAL <DATEJOUR, LOT> DATEEFFET_LOT)=DATEEFFET_LOT,CETAT_LOT,Null())

Regards,

Chris.