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.
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!
"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)
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.
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.
Actually, I think you've solved your own problem VERY cleanly. I think a pivot table is the right choice for displaying this information. I wish I'd thought of it.
You can use a simple sum(SO.VALUE) as an expression, but QlikView doesn't let you do that in a dimension. In an expression, QlikView KNOWS that you want to aggregate by ALL dimensions. But in a dimension, it has no idea what you want to aggregate by. That's where the aggr() comes into play, and why it is required in a dimension. In this case, you're telling it to aggregate by the SORDREF dimension. In other words, you're telling it to sum(SO.VALUE) for each SORDREF, and then you're listing that result next to the SORDREF. That's a great way to display this information. So I think you've solved your problem. :)
I also have a question if u can please help.
I am having a dataset which is having a Category lets say "A" and a subcategory lets call it "B" and an expression corresponding to each combination. Now I want to see the share of the expression for each "Category-Subcategory" combination in that category only. and subsequently I want to create a Bar Chart showing that value only greater than 80%. Please Help me on the same.