Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Aggregation in Stock Aging

Hi everybody!

I'm calling for help, 'cause my patience gave up.

Situation description: Dynamically calculate Stock Aging by periods using Stock and Purchase data. This can be done with FIFO method. We can fill the aging periods of an item with purchases (moving from later to earlier, i.e. youngest first) but not exceeding total quantity on stock. There are several warehouses.  Stocks could be grouped. In that case quantities on stock are summarized and all purchases between stocks are ignored (we will have a clear picture off items, purchased for group). For those purposes I created variable, what concatenates selected or possible warehouses.

In real situation there are a lot of additional tables. That's why relations between tables are as they are.

An issue: If we have an item in dimension, than everything calculates correctly. But looking at result without item dimension (summarized data) we can see some differences in Aging period numbers. That happens 'cause expression in summarized table operates with total sums of stock and purchases ignoring item level.

It is absolutely sure, that an aggregation is needed here. I tried to use it in many ways, but no result. Also I haven't found a better way to distract results of previous periods to have the rest of stock than a distracting column values. It is not the correct way, 'cause column data is for item group but not for each item.

The better way would be using such principle as

sum(rangemin(aggr(sum(Stock),Stock.Item) ,aggr(sum(Purchases),Stock.Item))),

but it is not working by me.

Definitely I am doing something wrong.

You are welcome with any suggestion!

2 Replies
Not applicable
Author

Look at here.

http://community.qlik.com/message/129126#129126

I think you can search in the forum  "inventory balance". I haveThe same problem.

Anonymous
Not applicable
Author

Hi, taxaw,

Seems, like you have quite similar needs. Hope we will get some help here.

Rrds,
AT