Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
amilafdo
Creator
Creator

Latest Unit Price for selected period

Hi All,

I wanted to display the latest price for the selected period with the vale. If anyone know this please help me to resolve.

Below are the example table. If I select the Apr 2017 BCD value should be  -> Qty 250, Value 500, Price 2. If I select the Jan 2018 BCD value should be -> Qty 200, Value 500, Price 2.5 . If I select both 2017 & 2018 BCD value should be -> Qty 450, Value 1000, Price 2.5 (Latest price).

Price Table:

 

ItemDATEPrice
ABC1/1/20173.5
BCD1/1/20172
DEF1/1/20183
ABC1/1/20182.7
BCD1/1/20182.5

Sales Table: 

ItemDATEQtyValue
ABC10/1/2017100350
BCD4/1/2017250500
DEF1/1/20182575
ABC

1/1/2018

350945
BCD1/1/2018200

500

Hope my requirement is clear. If you need further clarification please let me know.

Thanks

Amila Fernando

6 Replies
tresesco
MVP
MVP

I guess you would need: FirstSortedValue()

amilafdo
Creator
Creator
Author

Hi Tresesco B,

Thanks for your reply, I had tried with FirstSortedValue but I couldn't filter for date from Sales table.

Thanks

Amila

tresesco
MVP
MVP

How are you making the data model? Date fields from two tables should not be associated, I guess. You should probably just link tables only with Item field.

amilafdo
Creator
Creator
Author

Hi Tresesco B,

Can you please guide me how I can connect the 2 tables & FirstSortedValue() expression.

Thanks

Amila

tresesco
MVP
MVP

A deeper look at your tables says that it is possibly a SCD issue which could be resolved using intervalmatch() in the modelling like said here:IntervalMatch and Slowly Changing Dimensions

amilafdo
Creator
Creator
Author

Hi,

Any feedback for the above requirement. Provided link doesn't work.

Regards

Amila