topic calculation of Date & quantity in diff rows (availability) in QlikView

calculation of Date & quantity in diff rows (availability)

Wed, 01 Jul 2009 18:37:44 GMT

Here's a tough case:

How do you calculate the stock available after a sale by using two tables - one for the
sales and another for the deliveries?
I ran into the following problem: as I join the two tables of the sales and the

deliveries together, for each sale I have to get the delivered quantity for the period before the sale (Date>SupplyDate) and calculate the remaining quantity. I can't figure out how to make this work. Any ideas?

For example look at the file attached...

calculation of Date & quantity in diff rows (availability)

stephencredmond — Wed, 01 Jul 2009 19:44:33 GMT

Hi Konstantin,

Is the date of Sale/Delivery relevant? Is availability not All Deliveries - All Sales?

Stephen

calculation of Date & quantity in diff rows (availability)

Wed, 01 Jul 2009 20:26:45 GMT

Hi, Stephen.

The date of Sale/Delivery is not relevant. They happen at random. In principle, yes,
availability is All Deliveries - All Sales, but what I need is By Product.

I've got to know how much is the remaining quantity of each product at the point of sale.

calculation of Date & quantity in diff rows (availability)

stephencredmond — Wed, 01 Jul 2009 20:42:23 GMT

You could use an Aggr to get the delivery values:

Aggr(NODISTINCT Sum(DeliveryQTY),Product)

Stephen

calculation of Date & quantity in diff rows (availability)

Wed, 01 Jul 2009 23:41:35 GMT

Hi,

i believe the best is to change the load script.

Check the attached QVW.

Regards,

Joao