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

[swarnendu]

Thanks for your reply but its also not working.

[pradosh_thakur]

i changed the format . can you try now. have modified deva's solution too try that out as well.

[devarasu07]

Hi,

have your tried all my suggestion? is it working or still issue?

[tresesco]

Try including Master_Code as well in the aggr() like:

=firstsortedvalue(Aggr(Sum(Opening_Qty),Master_Code,New_MasterCode),-aggr(max({<Opening_Qty = {'<>0'}>} %Tran_Date),Master_Code,New_MasterCode))

If this doesn't work, try to share your sample qvw.

[swarnendu]

Its not working .

Please check my qvw file

[swarnendu]

Please check my qvw file

[pradosh_thakur]

You have %tran_date in the dimension so may be you  have to apply as-of-table approach here.

[swarnendu]

ya i dont need the date field but if i dont use then i cant explain my problem. so thats why i used it you remove and check the master code 2793

[tresesco · Accepted solution]

If you use date field in the chart then use:

Sum(If(%Tran_Date=Aggr(NODISTINCT Max({<Opening_Qty={'<>0'}>} %Tran_Date),MasterCode,New_MasterCode),Opening_Qty,0))

If no date field then:

FirstSortedValue(Aggr(Sum(Opening_Qty),%Tran_Date,New_MasterCode,MasterCode),-Aggr(Only({<Opening_Qty={'<>0'}>}%Tran_Date),%Tran_Date,New_MasterCode,MasterCode))

[swarnendu]

Thanks its working but now facing another problem.

when a master code value is zero then its also pick previous value

please look  at my screen shot. look the master code 7473 and 7481.

please suggest me if you have any solution .

Thanks and Regard

Swarnendu.