I've been trying to find a solutions to the following:
I have a sales table as follows:
Item
ItemGroup
Buyer
Month
Week
Qty
1
21
A
201001
1
82
2
33
A
201001
1
58
3
2
B
201001
1
76
4
3
B
201001
1
59
1
21
A
201001
2
77
2
33
A
201001
2
29
3
2
B
201001
2
55
4
3
B
201001
2
60
…
1
21
A
201205
1
55
2
33
A
201205
1
11
3
2
B
201205
1
96
4
3
B
201205
1
66
1
21
A
201205
2
86
2
33
A
201205
2
75
3
2
B
201205
2
74
4
3
B
201205
2
82
and Forecast table as follows:
ItemGroup
Buyer
Month
ForecastQty
21
A
201201
91
33
A
201201
10
2
B
201201
27
3
B
201201
4
21
A
201202
74
33
A
201202
55
2
B
201202
52
3
B
201202
67
21
A
201203
10
33
A
201203
12
2
B
201203
45
3
B
201203
7
21
A
201301
24
33
A
201301
3
2
B
201301
4
3
B
201301
87
Note that the sales are for many years and forecast for current fiscal year. I wanted to build a chart with current sales and forecast qty for each month. In order to do this I created a key from ItemGroup, Buyer and 2 right most digits of Month. The same key was created in sales table.
My problem is since the same key exists mutiple times in the sales table when I use right(Month,2) as my dimension and sum(ForecastQty) as expression I get multiples of the forecast Qty. In order to solve this problem I used sum(DISTINCT ForecastQty) but I have a feeling this is not the best solution.
Is there a way I can limit a single row for key from sales to be associated with the forecast? Rather than having a many to one connection I would like to force one to one from sales to forecast. This way my sum would be accurate and I wouldn't have to use sum(DISTINCT ForecastQty)?
I think the concatenate is ok as long as you don't have to display sales and forecast in same table. My needs are to display past 2 years sales, along with current sales and forecast in same table. Further more I have to be able to drill down to the buyer and product category.
I wanted to find join the forecast data with few records from my sales data. I know that creating a key that can be used to link the two tables is good but I need to elimintate Many to One (from sales to forecast) and create one-to-one connection somehow.
I still think that my solution would give you everything you need assuming I've understood correctly what you mean. I'd appreciate if you could create an example table using the tools on the forum (Advanced Editor --> Insert Table).
