topic Re: Latest Unit Price for selected period in QlikView

Latest Unit Price for selected period

amilafdo — Mon, 05 Nov 2018 03:07:24 GMT

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:

Item	DATE	Price
ABC	1/1/2017	3.5
BCD	1/1/2017	2
DEF	1/1/2018	3
ABC	1/1/2018	2.7
BCD	1/1/2018	2.5

Sales Table:

Item	DATE	Qty	Value
ABC	10/1/2017	100	350
BCD	4/1/2017	250	500
DEF	1/1/2018	25	75
ABC	1/1/2018	350	945
BCD	1/1/2018	200	500

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

Thanks

Amila Fernando

Re: Latest Unit Price for selected period

tresB — Mon, 05 Nov 2018 05:09:17 GMT

I guess you would need: FirstSortedValue()

Re: Latest Unit Price for selected period

amilafdo — Mon, 05 Nov 2018 05:37:03 GMT

Hi Tresesco B,

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

Thanks

Amila

Re: Latest Unit Price for selected period

tresB — Mon, 05 Nov 2018 05:46:50 GMT

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.

Re: Latest Unit Price for selected period

amilafdo — Mon, 05 Nov 2018 06:10:02 GMT

Hi Tresesco B,

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

Thanks

Amila

Re: Latest Unit Price for selected period

tresB — Mon, 05 Nov 2018 09:45:26 GMT

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

Re: Latest Unit Price for selected period

amilafdo — Wed, 14 Nov 2018 03:54:27 GMT

Hi,

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

Regards

Amila