8 Replies Latest reply: Feb 11, 2011 7:29 PM by John Witherspoon RSS

    Chart Expression: Sum of Value in One Table based on Date in another

    Joe Kirwan

      I have 2 tables:

      Sales Order Table:

      SO.DATE

      SORDREF

      Order Val

      Sales Invoice Table:

      SORDREF

      Invoice Date (as TR.DATE)

      Invoice Val

      The Sales Order can have multiple lines in the Sales Order Table; Each Sales Order line in the Sales Order Table can have a one to many relationship with lines in the Sales Invoice Table.

      I created a straight table showing the Date and Value (using a simple sum expression) for each Sales Order ie using ony fields from the Sales Order Table. As it calculates the Order Value, there is only one line item on the table for each order.

      Then I introduce the TR.DATE field from the Sales Invoice table. Where there is more than one TR.DATE for a Sales Order, the chart duplicates the total Sales Order value across each row for the order.

      Any suggestions.?

      error loading image

      Rgds

       

      Joe

        • Chart Expression: Sum of Value in One Table based on Date in another
          John Witherspoon

          What do you WANT to happen? Do you want it to display on only one of the rows? If so, which one? First one maybe? Something else? Why that row and not the others?

          I guess it just makes no sense to me to display order val by TR.DATE.

          Perhaps it makes more sense in the context of your real application, but for just what you show, I have no idea why you'd try to do this, and what you want to see differently if you DO do this.

          I'm not saying it can't be done. You probably could, for instance, display it on the first row for a given sales order. But I'd rather understand what you're after before I start looking for ways to achieve something like that.

            • Chart Expression: Sum of Value in One Table based on Date in another
              Joe Kirwan

              Hi John.

              Sorry for delay in responding - that's the difficulty of working across time-zones!

              I composed the following response. This process may have clarified my thinking, and maybe what I am trying to do is impossible. I'd like to hear what you think.

              I have no SQL or DBMS experience. This issue could be down to my shortcomings in this area. The post rambles on a bit, so if you run out of steam on this one, I'll understand!

              My response:

              "The Order file contains all the order line items. There is a unique identifier code for the order (SORDREF), and then separate unique identifier codes (ITEMREF) for each line item. So every line item in the Order file has a unique identifier code (ITEMREF).

              Order line items may be invoiced as a single invoice, or across multiple invoices. Each invoice line has a unique identifier code on the Invoice file. Each line will also contain both the Order and Order Line identifier from the Order file.

              Each Order has an Order Date (SO.DATE) on the Order file. Each invoice has a date on the Invoice file (TR.DATE).

              Each Order Line has a value in the Order File (Order Value). Each Invoice Line has a value in the Invoice File (TR.VALUE)

              The 2 files are linked in Qlikview via SORDREF. To avoid syn keys I renamed the Order Line Identifier (ITEMREF) as (TR.ITEMREF) in the Invoice file. (I'm not sure that it was necessary to do this - I noted your comments from previous posts regarding syn keys, and how they may be a good option in some cases. Anyway, I'm not sure it's relevant here)

              Finally, to answer your question.

              I want a table which shows for each Order Line (ITEMREF) in the Order file:

              1. The Original Value of that Order Line in the Order File (Order Value)

              2. The dates on which that Order Line was invoiced (in the Invoice File) (TR.DATE)

              3. The Value per Date of the Invoice Lines for that Order (in the Invoice File) (TR.VALUE)

              If I confine my report to ITEMREF-Order Value-TR.VALUE, I don't have any problems. Once I introduce the TR.DATE, then, for any ITEMREF that has more than one TR.DATE, the total Order Value for that ITEMREF repeats on each TR.DATE line. Follow?"

              End of response.

               

              On reflection, I may be trying to do the impossible, as I am trying to split a single line item in the Order File across multiple line items in the Invoice File, but with no link between the files that would enable that.

              I think I need to split my requirement across 2 reports. The first showing for each ITEMREF the Order Value and the Invoiced Value. The second, based on the invoice file only, showing the Invoice Value per date for those lines invoiced.

              Rgds

              Joe

               

              • Chart Expression: Sum of Value in One Table based on Date in another
                Joe Kirwan

                 

                Hi John

                I didn't find a solution to the problem , but may have found a workaround.

                I changed the chart from Straight Table to Pivot Table. I created a dimension for the expression Sum (SO.Value). (I initially tried this with simple Sum (SO.Value) - gave me an error. I found a forum posting that suggested use of AGGR. This worked. I don't know why, and I don't understand AGGR function, but that is for another day.)I removed the expression for SUM (SO.Value), and replaced it with Sum (TR.Value).

                Result is as follows.

                Joe