Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mazacini
Creator III
Creator III

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

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

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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. 🙂

View solution in original post

8 Replies
johnw
Champion III
Champion III

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.

mazacini
Creator III
Creator III
Author

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

mazacini
Creator III
Creator III
Author

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



johnw
Champion III
Champion III

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. 🙂

mazacini
Creator III
Creator III
Author

Thanks John. Praise indeed! I'm actually quite pleased with myself.

Also found your last post v. informative - it is little nuggets like these that make the forum so great. I don't think I'd find the Aggr so wel explained in the manual.

Joe

mazacini
Creator III
Creator III
Author

Thanks John. Praise indeed! I'm actually quite pleased with myself.

Also found your last post v. informative - it is little nuggets like these that make the forum so great. I don't think I'd find the Aggr so well explained in the manual.

Joe

Not applicable

Hi John

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.

Thanks

Regards

Saurabh

johnw
Champion III
Champion III

Sorry, I don't understand the question. Maybe you could show some example data and the chart you want, or post a sample QlikView?

Also, since this seems to have nothing to do with this particular thread, maybe you should start a new one?