Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have spent a lot of time on this issue and I am really stuck and I need your help
I have 4 tables
Calendar
calendar_date (has all dates)
year
period
Sales
calendar_date (has dates up to Today)
customer_code (specific customer codes where we made sales for)
net_sales
gross_sales
Budget
customer_group (all customer groups)
period
year
Customer
customer_code (all customer codes)
customer_group (all customer groups)
The above are the four tables, and the issue i am having is;
I need to create a link between them, we have a link that is working fine but the Budget data brings only for the current period, as the sales table has specific dates and customer codes, so it reduces the data for all
Existing Model: If there is no Sales in a month for a customer, then no budget (target) is not showing on the reports. (Sales joins Customer joins Target)
New Model: If we create link between Calendar and Target (blue line) then it creates circular reference?
what i am trying to get is; regardless of dates in Sales or customer_code exist or not I want to show the budget based the Customer group but when a year, period is selected then it filters for this period or year
I have tried new link table, concatenate data , join data to sales but none has worked
I need your help
The easiest approach for such a usecase is just to concatenate sales and budget. In your case you adds the customer group per join/mapping from your customer table to sales and creates per makedate(year, period, 1) a date within the budget-load.
- Marcus
The easiest approach for such a usecase is just to concatenate sales and budget. In your case you adds the customer group per join/mapping from your customer table to sales and creates per makedate(year, period, 1) a date within the budget-load.
- Marcus