topic Summing the Header versus the detail in QlikView

Summing the Header versus the detail

Mon, 16 Apr 2012 13:43:34 GMT

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

Summing the Header versus the detail

CELAMBARASAN — Mon, 16 Apr 2012 13:48:57 GMT

Hi,

Can you post the expression?Also provide by which field you determine it is balance or transaction?.

Celambarasan

Summing the Header versus the detail

Mon, 16 Apr 2012 14:14:25 GMT

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.

Summing the Header versus the detail

Mon, 16 Apr 2012 17:34:53 GMT

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")?

Summing the Header versus the detail

jonathandienst — Mon, 16 Apr 2012 18:26:24 GMT

Its sounds like on of the two fields is located in the transactions file. Is this correct?

Regards

Jonathan

Re: Summing the Header versus the detail

Mon, 16 Apr 2012 18:43:25 GMT

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.

Summing the Header versus the detail

jonathandienst — Tue, 17 Apr 2012 15:08:07 GMT

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

Re: Summing the Header versus the detail

Tue, 17 Apr 2012 15:11:59 GMT

is one of fields in your transaction table named cost or balance?

Summing the Header versus the detail

Tue, 17 Apr 2012 15:18:28 GMT

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.

Re: Summing the Header versus the detail

Tue, 17 Apr 2012 15:54:27 GMT

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)

I know this is not correct because it doesn't come out to the correct numbers (ran a manual query). I know if I took out the transaction file this formula would work but I need the transaction file for other things I want to do in the dashboard.

Summing the Header versus the detail

Tue, 17 Apr 2012 16:02:23 GMT

Are you using Plant as a dimension? If you are not and a product is in more than one plant you will get the weight twice.

You could try this and see how it works.

sum(aggr(sum(end_balance * weight),plant_name, close_period))

If this does not help can you post a copy of your actual script?

Re: Summing the Header versus the detail

Wed, 18 Apr 2012 12:17:24 GMT

Yes, that is where I made my mistake, when I setup the data structure I

removed the key fields from the header and left them in the detail. Once

I swapped which table the fields were coming from everything came back

into order.

Thanks for your help.

From:

marcsliving <qliktech@sgaur.hosted.jivesoftware.com>

To:

scottsimmons <scottsimmons@bp.guardian.com>

Date:

04/17/2012 11:18 AM

Subject:

- Re: Summing the Header versus the detail

QlikCommunity

Re: Summing the Header versus the detail

created by marcsliving in New to QlikView - View the full discussion

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.

Reply to this message by replying to this email -or- go to the message on

QlikCommunity

Start a new discussion in New to QlikView by email or at QlikCommunity

| Terms of Use | Software EULA

Re: Summing the Header versus the detail

Wed, 18 Apr 2012 12:18:00 GMT

I made my mistake, when I setup the data structure I removed the key

fields from the header and left them in the detail. Once I swapped which

table the fields were coming from everything came back into order.

Thanks for your help.

From:

Juan Fibonacci <qliktech@sgaur.hosted.jivesoftware.com>

To:

scottsimmons <scottsimmons@bp.guardian.com>

Date:

04/17/2012 11:11 AM

Subject:

- Re: Summing the Header versus the detail

QlikCommunity

Re: Summing the Header versus the detail

created by Juan Fibonacci in New to QlikView - View the full discussion

is one of the name of your fields in your transaction table cost or

balance?

Reply to this message by replying to this email -or- go to the message on

QlikCommunity

Start a new discussion in New to QlikView by email or at QlikCommunity

| Terms of Use | Software EULA

Re: Summing the Header versus the detail

Wed, 18 Apr 2012 12:19:12 GMT

I made my mistake, when I setup the data structure I removed the key

fields from the header and left them in the detail. Once I swapped which

table the fields were coming from everything came back into order.

Thanks for your help.

From:

Jonathan Dienst <qliktech@sgaur.hosted.jivesoftware.com>

To:

scottsimmons <scottsimmons@bp.guardian.com>

Date:

04/17/2012 11:07 AM

Subject:

- Re: Summing the Header versus the detail

QlikCommunity

Re: Summing the Header versus the detail

created by Jonathan Dienst in New to QlikView - View the full discussion

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

Reply to this message by replying to this email -or- go to the message on

QlikCommunity

Start a new discussion in New to QlikView by email or at QlikCommunity

| Terms of Use | Software EULA