Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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