Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
swarnendu
Creator II
Creator II

how to pick max date wise value for each row?

Hi all,

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

errpppp.jpg

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

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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))



View solution in original post

26 Replies
devarasu07
Master II
Master II

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
Master
Master

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
swarnendu
Creator II
Creator II
Author

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

swarnendu
Creator II
Creator II
Author

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

shiveshsingh
Master
Master

hi

Can you share the expected output?

pradosh_thakur
Master II
Master II

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

Learning never stops.
swarnendu
Creator II
Creator II
Author

I used this but show me null value

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

pradosh_thakur
Master II
Master II

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

Learning never stops.