Display most recent value if there's no record for such date

[therealdees]

Hi

I have a model built with link tables where I relate inventory and sales facts using a deposit_key, store_key, calendar_key and product_key. Every store have more than 1 deposit. The sum of every deposit is equal to the total products available in that store, but the data is limited to a snapshot by the end of each month, so I use monthend(sale_date) to be able to create a composite key for the link table.

In the sales fact table there's a column holding the "original price" for a product. This column is added during the ETL steps and it uses a price history table by interval match to add the correct price.

The problem is that when I add the product_sku as dimension and measure the total available quantity and also the total revenue for instance, it will only show the original price for the product_sku that has been sold in the selected period, obviously because the values are dimensioned by the sales fact.

How could I show the most recent price to the period selection for items that had no sales and/or have 0 as available quantity?

I tried to elaborate something with FirstSortedValue but it didn't work...

Any suggestion is welcome, thanks

[Oleg_Troyansky]

Hi there,

I'd try to place the "most recent price" field in the Link table - whether you have sales and/or inventory, you should have a corresponding row in the Link Table. If you add the price field there, it will always be available to you.

If you need to have the most recent price for months in which you have neither sales nor available inventory, then you'd have to either generate extra rows in the Link Table for those months, or come up with some complicated AGGR() formula to fetch that last available value in the layout...

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!