13 Replies Latest reply: Apr 18, 2012 8:19 AM by Scott Simmons RSS

    Summing the Header versus the detail

      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 Adhimulam

          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

              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

                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
                    Jonathan Dienst

                    Hi

                     

                    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

                        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
                            Jonathan Dienst

                            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

                              • Re: Summing the Header versus the detail

                                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.

                                • Re: Summing the Header versus the detail

                                  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

                                  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

                                  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

                                   

                                  © 1993-2011 QlikTech International AB     Copyright & Trademarks | Privacy

                                  | Terms of Use | Software EULA

                                • Re: Summing the Header versus the detail

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

                                    • Re: Summing the Header versus the detail

                                      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

                                       

                                      © 1993-2011 QlikTech International AB     Copyright & Trademarks | Privacy

                                      | Terms of Use | Software EULA

                                    • Summing the Header versus the detail

                                      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

                                          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

                                           

                                          © 1993-2011 QlikTech International AB     Copyright & Trademarks | Privacy

                                          | Terms of Use | Software EULA