Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In my application I have a balance table for the month and transactions that are generated that apply to the month. What I want to do is sum the balances over time but not have it inflated by the transactions for that month. I believe this falls into the classic case of header / detail, because the detail has been purged, I no longer can just some the detail. I'm a newbie so I'm not sure how to enter the expression.
Thanks
Hi,
Can you post the expression?Also provide by which field you determine it is balance or transaction?.
Celambarasan
My balance file is by month so it's a simple expression sum(end_balance * avg cost) which gives me the value of our inventory over time. However, since I have the balance file linked to the transactions(ie production, adjustments and shipments) it is inflating the value.
Let's say I had the two files (many to one relationship) linked together by a "key field". Is there a way to do a sum(value distinct "key field")?
Hi
Its sounds like on of the two fields is located in the transactions file. Is this correct?
Regards
Jonathan
One is the balance file and the other is a transaction file linked by a keyfield(year, month, product). The balance file has the balance for the entire month and that is linked to all the tranactions for the month. What I want to do is just get the value of inventory (balance * cost) for the month(stored in balance file) and I am using year and month as my dimension. However, since I have it linked to the transactions it causes the number to be exagerated and so I need to find a way to sum based on just the data in the balance file.
Hi
I am still not clear on the details of your data structure so its hard to make specific suggestions. Could you post an example file?
Regards
Jonathan
is one of fields in your transaction table named cost or balance?
It sounds like you are joining the tables. If you use a keep instead of join and your expression comes from the header table you should get the correct results.
I have a balance table that has the beginning and ending balance for every product for every plant
table
plant
period (01/01/11, 02/01/11, 03/01/11, etc)
product
beg balance
end balance
I also have a transaction table that contains transactions for the same period (ie production, shipment and adjustments)
table
plant
period
product
transaction type (production, shipment, adjustment, etc)
transaction date
quantity
My last table contains the product master that has all the properties of a product.
table
product
description
weight
I created a key field (plant, period and product) to link the balance table and the transaction table together. The way they monitor inventory is by total pounds so what I wanted to do for every plant and period show the total pounds of inventory. So I made chart with close period and plant as my dimensions and put in the following formula: sum(total <plant_name, close_period> end_balance * weight)