topic Re: how to pick max date wise value for each row? in QlikView

how to pick max date wise value for each row?

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

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

Re: how to pick max date wise value for each row?

devarasu07 — Fri, 29 Dec 2017 07:54:49 GMT

Hi,

you can try like below methods,

Method 1: using set analysis

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

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

=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

Re: how to pick max date wise value for each row?

shiveshsingh — Fri, 29 Dec 2017 07:59:46 GMT

You can try this

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

Re: how to pick max date wise value for each row?

PrashantSangle — Fri, 29 Dec 2017 08:01:20 GMT

did you check firstSortedValue()

see below

FirstSortedValue - chart function ‒ QlikView

Regards

Re: how to pick max date wise value for each row?

swarnendu — Fri, 29 Dec 2017 08:05:59 GMT

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

Re: how to pick max date wise value for each row?

swarnendu — Fri, 29 Dec 2017 08:07:33 GMT

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

Re: how to pick max date wise value for each row?

shiveshsingh — Fri, 29 Dec 2017 08:09:41 GMT

Can you share the expected output?

Re: how to pick max date wise value for each row?

pradosh_thakur — Fri, 29 Dec 2017 08:11:30 GMT

try this

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

=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

Re: how to pick max date wise value for each row?

swarnendu — Fri, 29 Dec 2017 08:14:19 GMT

I used this but show me null value

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

Re: how to pick max date wise value for each row?

pradosh_thakur — Fri, 29 Dec 2017 08:14:42 GMT

Slight modification to what deva suggested

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

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

edit: changed the format

Re: how to pick max date wise value for each row?

swarnendu — Fri, 29 Dec 2017 08:18:51 GMT

Thanks for your reply but its also not working.

Re: how to pick max date wise value for each row?

pradosh_thakur — Fri, 29 Dec 2017 08:20:48 GMT

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

Re: how to pick max date wise value for each row?

devarasu07 — Fri, 29 Dec 2017 08:21:55 GMT

Hi,

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

Re: how to pick max date wise value for each row?

tresB — Fri, 29 Dec 2017 08:32:42 GMT

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.

Re: how to pick max date wise value for each row?

swarnendu — Fri, 29 Dec 2017 08:54:45 GMT

Its not working .

Please check my qvw file

Re: how to pick max date wise value for each row?

swarnendu — Fri, 29 Dec 2017 08:59:21 GMT

Please check my qvw file

Re: how to pick max date wise value for each row?

pradosh_thakur — Fri, 29 Dec 2017 09:19:17 GMT

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

Re: how to pick max date wise value for each row?

swarnendu — Fri, 29 Dec 2017 09:40:27 GMT

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

Re: how to pick max date wise value for each row?

tresB — Fri, 29 Dec 2017 09:50:56 GMT

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

Re: how to pick max date wise value for each row?

swarnendu — Fri, 29 Dec 2017 10:40:20 GMT

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.