Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Thanks for your reply but its also not working.
i changed the format . can you try now. have modified deva's solution too try that out as well.
Hi,
have your tried all my suggestion? is it working or still issue?
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.
Its not working .
Please check my qvw file
Please check my qvw file
You have %tran_date in the dimension so may be you have to apply as-of-table approach here.
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
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))
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.