We need some suggestions for solving this calculation.
We have stores that perform daily inventory levels of various products.
Some stores do not report their levels every day, and may skip several days.
How do I provide a table of the actual inventory level on any given day for every store, even if a store did not report on the day requested, it picks the last inventory reported.
Example: Store 1 reports on Jan 1, 2,5,6; Store 2 reports on Jan 1,3,6; store 3 reports on Jan 1, 4, 6
I pick date Jan 4 to see levels as of Jan 4, the table should show the inventory levels for store 1 from Jan 2 ; store 2 from Jan 3 and store 3 from Jan 4.
My recommendation is to fill missing days with the previous values. I've answered a similar question a while ago, and still have the example - see attached.
That's expected. You have first to create a list of all dates (calandar), and join it with the existing data, and only after that fill the missing days.
Sure it should be sorted properly. In this simple example it's sorted in the datasource itself, so I didn't bother. BTW, I had a problem using this method when loading data from a database, although it worked fine from the file. As a workaround, I had to load data from the database into the qvd file, and fill the blanks while reading from qvd.
There is a similar example in the QV Cookbook that includes generating the range of dates. See the sample titled "Fill values in a data range using previous values.".
, my apology.
