how to pick max date wise value for each row?

[swarnendu]

Hi all,

I have a table showing Master_Code, Master_Code_NO , dates and quantities:

I want to pick the value   for each master code number using by an expression the last date value in the table

Can someone let me know how ?

i used the expression is

=Sum({<%Tran_Date={'$(vTEST)'}>}Opening_Qty)

where

vTEST =(max({<Opening_Qty = {'<>0'}>}%Tran_Date))

thx.

Swarnendu

[devarasu07]

Hi,

you can try like below methods,

Method 1: using set analysis

=Sum( {$<%Tran_Date ={'$(=Max(Date))'}, Opening_Qty={">0"} >} Opening_Qty)

or

=Sum( {$<%Tran_Date ={'$(=Max(Date))'}, Opening_Qty-={0} >} Opening_Qty)

or

=Sum( {$<%Tran_Date ={'$(=Max(Date))'},Opening_Qty={"=sum(Opening_Qty)<>0"} >} Opening_Qty)

also make sure your date format,

Sum({$<%Tran_Date = {"$(=Date(Max(%Tran_Date), 'DD/MM/YYYY'))"},Opening_Qty-={0}>} Opening_Qty)

note: update your current date format in above code

Method 2:

create calculated dimension,

aggr(Max(Date), dimension)

or max(Date)

and then create measure as

sum( {$<Opening_Qty-={0} >} Opening_Qty)

Method 3:  using FirstSortedValue

FirstSortedValue(Opening_Qty, -Aggr(Max(%Tran_Date ),your dimension))

Note, if still not working, would suggest you to provide the sample app with mock data & expected output in excel format, that would be easy for us to check and help u quickly.

Thanks,

Deva

[shiveshsingh]

Hi

You can try this

Sum( {$<%Tran_Date = {"$(=Max(Date)"}, Opening_Qty={!=0"} >} Opening_Qty)

[PrashantSangle]

did you check firstSortedValue()

see below

FirstSortedValue - chart function ‒ QlikView

Regards

[swarnendu]

Its not working..Return me same value like my screen shot

[swarnendu]

Its not working..Return me same value like my screen shot

[shiveshsingh]

hi

Can you share the expected output?

[pradosh_thakur]

try this

=Sum({<%Tran_Date={$(vTEST)}>}Opening_Qty)

OR

=Sum({<%Tran_Date={"$(vTEST)"}>}Opening_Qty)

where

vTEST    =date(max({<Opening_Qty = {">0"}>}%Tran_Date),'DD/MM/YYYY')  //Dont forget the equal to

edit:changed the format

[swarnendu]

I used this but show me null value

=firstsortedvalue(Opening_Qty,-aggr(max(%Tran_Date),New_MasterCode))

[pradosh_thakur]

Slight modification to what deva suggested

=Sum( {$<%Tran_Date ={'$(=date(Max(Date),'DD/MM/YYYY'))'}, Opening_Qty={">0"} >} Opening_Qty)

or

=Sum( {$<%Tran_Date ={'$(=DATE(Max(Date),'DD/MM/YYYY'))'}, Opening_Qty-={0} >} Opening_Qty)

edit: changed the format