Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Another question: I have two tables with a one to many relationship. I want a count from the table where there is only one record and a sum on the table with many records. How do I do that?
Hi,
Suppose that you have two table below
1. Invoice header:
Inv | Customer |
1 | A |
2 | B |
3 | C |
2. Invoice details
Inv | Item | Amount |
1 | I001 | 10 |
1 | I002 | 20 |
1 | I003 | 30 |
2 | I001 | 40 |
2 | I002 | 50 |
So you link both table via [Invoice header],[Inv]=[Invoice details].[Inv]
From this we will got one to many relationship.
Now try to create Straight table with
1. Dimension: Inv and Customer
2. Expression
a. Count(Inv)
b. Sum(Amount)
It should give you
Inv | Customer | Count(Inv) | Sum(Amount) |
1 | A | 1 | 60 |
2 | B | 1 | 90 |
3 | C | 1 | 0 |
Regards,
Sokkorn