I am budgeting for many stores and based on opening date they can be grouped into three catagories comp (these stores have histroy to budget on), noncomp (these could be budgeted based on an annualized last month of the year) and new (these need to based only on assumptions)
The store list with open dates are located in a data warehouse table maintained by the stores organization.
I would like to have my store number list updated by queries on the sql data store and utilized using ODBC.
Hello Milo5255 – Thanks for your post:
Here is an approach. I mimicked a simple database in the attached Excel file. It contains Store Number and an Opening Date.
In the attached model file, I linked to the excel file via ODBC. I have three DataLinks going to three different matrices.
– Stores open more than one year
– Stores open less than one year but more than one month
– Stores open less than one month
In each of the DataLinks, a qualifier is used to test how long the store has been open. Here is an example of the more than one year qualifier.
StoreOpen.”Opening Date” < ( now( ) – 365 )
So this qualifier only brings in stores open more than 365 days ago from today.
On the last DataLink wizard panel, I have a check mark in the box “Remove previously imported category items and data not present in update”. When a store New store reaches 1 month, it will automatically go from the ‘new’ store matrix to the ‘< 1 year > 1 month’ matrix.
So download the files and try it out. Please feel free to post again if you have further questions.