Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I have two table with matching fields. And I need to represent them both in a chart if possible(sum of Sales per per week). Both table consist of some figures per category oevr a period of time but they can not be merged. The should be combined into one graph to provide a visual comparison of the actual sales and budget targets. Maybe someone can assist with this. Currently I have two separate graphs showing the calculations per week. Also, Table2 consists of only current year figures (so I need to display only this years data); budget is estimated for each month of the year and actual sales table consists of only data starting from the beginning of the year until current week. Examples of the table sare presented below.
Table1-Actual Sales
Item | Category | EUR | Year | Month |
ItemA | Category1 | 209,40 | 2014 | 1 |
ItemB | Category2 | 500,17 | 2015 | 2 |
ItemC | Category3 | 315,82 | 2015 | 1 |
ItemD | Category2 | 740,11 | 2014 | 2 |
ItemE | Category2 | 599,51 | 2014 | 3 |
ItemF | Category3 | 880,90 | 2015 | 2 |
ItemG | Category2 | 772,05 | 2015 | 1 |
Table2-Budget
Category | EUR | Year | Month |
Category1 | 529,85 | 2015 | 1 |
Category2 | 874,50 | 2015 | 2 |
Category3 | 508,78 | 2015 | 1 |
Category1 | 830,31 | 2015 | 1 |
Category2 | 359,12 | 2015 | 2 |
Category3 | 432,19 | 2015 | 3 |
Since the data cannot be combined, try to build a composite key between the two tables , that will server the purpose I guess
composite key like :
Actual_Sales:
Load
Category&'-'&Year&'-'&Month as Key,
....
Budget:
Load
Category&'-'&Year&'-'&Month as Key,
....
..
This will give the desired result
Table2-Budget
Category | EUR | Year | Month |
Category1 | 529,85 | 2015 | 1 |
Category2 | 874,50 | 2015 | 2 |
Category3 | 508,78 | 2015 | 1 |
Category1 | 830,31 | 2015 | 1 |
Category2 | 359,12 | 2015 | 2 |
Category3 | 432,19 | 2015 | 3 |
Why do you have two rows for the same Category for the same period of the same year? Which one has the correct budget for the category?
How about loading your budget figures into your sales table?
map_budget:
MAPPING LOAD
Category&Year&Month,
EUR
FROM
your budget table
Sales:
LOAD Item,
Category,
EUR as Sales,
Year,
Month,
Applymap('map_budget', Category&Year&Month) as Budget
FROM
your sales table