My data model has attributes on two different levels currently: “Brand Level” and “Store Level”. A Brand can have 1 or more Stores linked to it.
For my Brands, I have a table called “Trended Brand” which has data for each Brand on a month-end level. This table has ~120 columns and has month-end dates going all the way back to 2007. Below I have listed an example of 3 columns “Brand ID”, “As Of Date” & “Profit” for a few Brands and 3 dates
Brand ID
As Of Date
Brand Profit
AAAA
1/31/2018
$200
BBBB
1/31/2018
$1,000
CCCC
1/31/2018
$2,000
AAAA
2/28/2018
$180
BBBB
2/28/2018
$900
CCCC
2/28/2018
$1,800
AAAA
3/31/2018
$162
BBBB
3/31/2018
$810
CCCC
3/31/2018
$1,620
For Store level data, there is a table called “Store Current” which has all attributes related to different stores. This table has ~80 columns. Below I have listed an example of this table with just 3 columns for 6 stores.
Store ID
Store State
Store Size
1111
NY
Large
2222
CA
Large
3333
NY
Large
4444
NJ
Small
5555
NJ
Medium
6666
PA
Medium
To link Stores to Brands, I have a table called “Linkage” which is two columns with “Store ID” & “Brand ID”. This shows how a Brand can link to multiple different Stores.
Brand ID
Store ID
AAAA
1111
BBBB
2222
BBBB
3333
CCCC
4444
CCCC
5555
CCCC
6666
The data model question that I have is about how to best add trended month-end Store data. For example, I would like to be able to show the number of employees at each Store from 2007 until now. (As well as other attributes). I could create a table like below where I have Store ID which would cause this table to join to my “Store Current” & “Linkage” table. I would be introducing a new date column which would be “Store As Of Date”. I don’t love this because frequently our sheets are set up for someone to select a “As Of Date” and then view Brand & Store Level attributes. Almost always I want to have “As Of Date” & “Store As Of Date” to be the same date. For example, I would want to create a sheet that would have the user select an “As Of Date” and then I would have a table that would be Brand ID, SUM(Profit) & SUM(Store Employees). But I would want Store Employees to be based off of the same date that Profit is being taken from. A user may also think that "Store Employees" is in my "Store Current" table and not realize that they would be summing Employees for all dates if they didn't set Store As Of Date.
Store ID
Store As Of Date
Store Employees
1111
1/31/2018
100
2222
1/31/2018
90
3333
1/31/2018
110
4444
1/31/2018
25
5555
1/31/2018
88
6666
1/31/2018
90
1111
2/28/2018
95
2222
2/28/2018
96
3333
2/28/2018
105
4444
2/28/2018
24
5555
2/28/2018
84
6666
2/28/2018
86
1111
3/31/2018
90
2222
3/31/2018
91
3333
3/31/2018
100
4444
3/31/2018
23
5555
3/31/2018
80
6666
3/31/2018
82
Is there any way that I could introduce some sort of functionality that would set both dates to the same value? So on my sheet I could have one filter that would set both date fields. Could I use master calendar or something for this?
I thought about creating a “Date Bridge” table which would be two columns with concatenations of Store ID & Brand ID along with dates. Then my trended Store level data would have “Store ID – Date” as the unique Identifier and I would add a column "Brand ID - Date" to my "Trended Brand" table.
Brand ID - Date
Store ID - Date
AAAA - 1/31/2018
1111 - 1/31/2018
BBBB - 1/31/2018
2222 - 1/31/2018
BBBB - 1/31/2018
3333 - 1/31/2018
CCCC - 1/31/2018
4444 - 1/31/2018
CCCC - 1/31/2018
5555 - 1/31/2018
CCCC - 1/31/2018
6666 - 1/31/2018
Store ID - Date
Store Employees
1111 - 1/31/2018
100
2222 - 1/31/2018
90
3333 - 1/31/2018
110
4444 - 1/31/2018
25
5555 - 1/31/2018
88
6666 - 1/31/2018
90
This would help solve my issue of only having to set 1 date for my Brand & Store level data. But now I run into the issue where my “Store Current” table doesn’t really correlate to my “Store Trended” data. For example, if I still tried to show the same dashboard (Brand with Total Profit & Total Employees) and then set the “As Of Date” but also filtered for “Store State” = “NY”. This would limit my population to Brand IDs AAAA & BBBB because Stores "1111" & "3333" would have been selected. My SUM(Store Employees) would include all underlying Stores for those two brands for that As Of Date. That calculation would not take into account that Store 2222 is actually has Store State equal to “CA”.
Please let me know if you have any thoughts or questions.
I can think of two options, assuming your data model has the two different dates:
Have the user select a single date using "As of Date" (in a filter, table or elsewhere). Assign that value to a variable. Use the variable as a filter in visualizations containing "Store as of Date".
Have the user select one or more dates using "As of Date" (in a filter, table or elsewhere). Use set analysis P function ([Store as of Date]=p([As of Date])) to filter visualizations containing "Store as of Date."
its also possible to create a separate island calendar and use that for selection and use set analysis to filter but not recommended for performance reasons
