ddlyturtle
New Member
- Joined
- Jan 11, 2019
- Messages
- 3
Hi,
I have been scratching my head for some time now trying to figure out how to layout my table structure. I have one table that gets data of sales imported from several databases manually but salesperson is a related key so now connected to a second table to track if the salesperson is a homeseller and what brand he/she focuses on etc. Just to expand potential ways for statistics.
eg.
<tbody>
</tbody>
<tbody>
</tbody>
However, I want each salesperson to have something i now call "EmployeePeriod". As they are employed elsewhere and they might change brand but i do not get this information from any imported data. So i'll have/want to manually edit this table as changes will rarely happen, instead of manually entering this to every import(which contains 60k rows).
I want the historic data to stay related to that persons row as it was related to that period. (eg. Adam sells Whiskers for 2015-01-01 - 2016-01-01) while 2016-01-02 the brand was changed to Skittles.
so for all sales from the date 2016-01-02 Adams sales will be labeled skittles, and all data for 2015-01-01 - 2016-01-01 will be still be categorized as Whiskers.
Problem 2:
With the same current tables, i'd like to track how many days that salesperson was selling whiskers depending on which date i am looking at in the pivot. To compare if numbers of sales increased with time spent within that period/brand. Can't figure a clever way to solve this.
Eg in pivot if i filter the dates between 2015-01-01 and 2015-12-31 i'll see a column with linear datedif up until a new "employeeperiod" starts. something like (=Datedif(Table1!Date, Table2!EmployeePeriod,"d") but when employeeperiod changes it becomes troublesome to somehow keep the historical data to previous employeeperiod.
I'm open for suggestions..
I have been scratching my head for some time now trying to figure out how to layout my table structure. I have one table that gets data of sales imported from several databases manually but salesperson is a related key so now connected to a second table to track if the salesperson is a homeseller and what brand he/she focuses on etc. Just to expand potential ways for statistics.
eg.
Date | Salesperson | city | product | salestotal |
2015-01-02 | Adam | London | Catnip | 100 |
<tbody>
</tbody>
Salesperson | Location | EmployeePeriod | Brand |
Adam | Home | 2015-01-01 | Whiskers |
Ben | Away | 2016-03-01 | Skittles |
<tbody>
</tbody>
However, I want each salesperson to have something i now call "EmployeePeriod". As they are employed elsewhere and they might change brand but i do not get this information from any imported data. So i'll have/want to manually edit this table as changes will rarely happen, instead of manually entering this to every import(which contains 60k rows).
I want the historic data to stay related to that persons row as it was related to that period. (eg. Adam sells Whiskers for 2015-01-01 - 2016-01-01) while 2016-01-02 the brand was changed to Skittles.
so for all sales from the date 2016-01-02 Adams sales will be labeled skittles, and all data for 2015-01-01 - 2016-01-01 will be still be categorized as Whiskers.
Problem 2:
With the same current tables, i'd like to track how many days that salesperson was selling whiskers depending on which date i am looking at in the pivot. To compare if numbers of sales increased with time spent within that period/brand. Can't figure a clever way to solve this.
Eg in pivot if i filter the dates between 2015-01-01 and 2015-12-31 i'll see a column with linear datedif up until a new "employeeperiod" starts. something like (=Datedif(Table1!Date, Table2!EmployeePeriod,"d") but when employeeperiod changes it becomes troublesome to somehow keep the historical data to previous employeeperiod.
I'm open for suggestions..