Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
andrew_sell
New Contributor II

Max Price based on Max Date

Hi,

I am looking for some help on how to obtain the most recent price of an item that has been purchased multiple times. I want to display the price based on the Max date in a table. My raw data set looks like below. 

PO DateItemPO DescrPrice USD
2/22/2019A6610407277TAPE; SEALANT, PTFE, 1-1/2" W X 520' L15.28
2/4/2019A6610407277TAPE; SEALANT, PTFE, 1-1/2" W X 520' L15.28
12/20/2018A6610407277TAPE; SEALANT, PTFE, 1-1/2" W X 520' L15.27
10/8/2018A6610407277TAPE; SEALANT, PTFE, 1-1/2" W X 520' L15.27
11/10/2017A6610000454BEARING,BALL:FAFNIR,205KDD,CONRAD,SINGLE11.39
3/13/2017A6610000454BEARING,BALL:FAFNIR,205KDD,CONRAD,SINGLE11.83
2/2/2016A6610000454BEARING,BALL: FAFNIR MFG, 205KDD MFG P/N, CONRAD TYPE, SINGLE ROW, 0.9843" INSIDE DIAMETER, 2.0472" OUTSIDE DIAMETER, 0.5906" WIDTH, (2) SHIELD STYLE, 25BC02JPP AFBMA, 11561 IBI11.69
10/22/2018A6610407096SEAL GENERAL 63INFABRISEAL 8"WX19LF2348.18
3/13/2017A6610407096SEAL GENERAL 63INFABRISEAL 8"WX19LF2268.93
12/5/2016A6610407096SEAL GENERAL 63INFABRISEAL 8"WX19LF2268.93
11/15/2016A6610407096SEAL GENERAL 63INFABRISEAL 8"WX19LF2268.93

 

And what i would like to display would be below:

PO DateItemPO DescrPrice USD
2/22/2019A6610407277TAPE; SEALANT, PTFE, 1-1/2" W X 520' L15.28
11/10/2017A6610000454BEARING,BALL:FAFNIR,205KDD,CONRAD,SINGLE11.39
10/22/2018A6610407096SEAL GENERAL 63INFABRISEAL 8"WX19LF2348.18
Labels (1)
5 Replies
Partner
Partner

Re: Max Price based on Max Date

Prices:
Load
   [PO Date] ,
   Item,
   [PO Descr],
   [Price USD]
FROM 
   RawData
;
Right join (Prices)
Load
   Max([PO Date]) as [PO Date],
   Item
Resident 
   Prices
Group by 
   Item
;
#OnVacation #BetweenJobs.
Please ekskuse my Norglish and Swenglish typos.
Partner
Partner

Re: Max Price based on Max Date

Hi Andrew,

I think you could use FirstSortedValue.

FirstSortedValue(Item, -PO Date) would show the first value of Item based on the max date.

I would create a table with the following columns:

PO Date
FirstSortedValue(Item, -PO Date)
PO Descr
Price

Regards,

Juan

 

trdandamudi
Honored Contributor

Re: Max Price based on Max Date

In a straight table create as below:

Dimension:

[PO Date]

Item

[PO Descr]

 

Expression:

=Aggr(Max([Price USD]),Item)

 

Hope this helps.

Shubham_Deshmukh
Valued Contributor

Re: Max Price based on Max Date

Pls confirm, you value for 10/22/2018 is 2348.18 ? or it should be 15.27.
andrew_sell
New Contributor II

Re: Max Price based on Max Date

Value should be 2348.18 as that is the newest price for that Item ID