Calculated dimension based on max value

leenlart — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculated dimension based on max value

chrismarlow — Tue, 28 Aug 2018 15:20:34 GMT

Maybe something like this;

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

Regards,

Chris.

topic Calculated dimension based on max value in QlikView

Calculated dimension based on max value

Re: Calculated dimension based on max value