I have excel spreasheet that calculates numbers based on weekly input of rates. This then gets fed into dashboard manually.
I am trying to automate the process, I am trying to figure out the best approach of how to do this, where rates are entered in weekly basis and that will automatically feed the numbers to dashboard.
currently it is laid out like below
Week #: 2
Rates: $$$$
based on above characteristics, excel sheet will go to calendar dates that lies in week 2 and do the calculation applying that rate for entire week2. after which user takes the data and updates Access db, which then feeds into qlikview, cycle happens every week.
Whats the best of automating this. how do you handle weekly changing stuff in qlikview.
You can directly read this excel file where you maintain week rates with value after making this excel. Read this excel in separate qvw application and make QVD of this and then read this qvd in your data model. In this step you have to updated only excel file and schedular automatically reads your file and make qvd.
Eg:-
1. Excel file -> QVD Generate qvw -> QVD
2. QVD -> YourDatamodel.
3. Update excel file into MS-Access.(if require for future use but you are also creating QVD data i thing no need)
So i was thinking along the same line here is my dilemma.
This is how my input database need to be set-up.
User will enter this info on weekly basis
Period No
Week No
Canadian Dollar rate
US Dollar Rate
British Pound Rate
1
1
10%
10%
10%
1
2
20%
20%
20%
1
3
30%
30%
30%
1
4
40%
40%
40%
2
5
50%
50%
50%
Based on that another sheet or access database needs to get populated which will look like this.
Date
Period
Year
Working Week of Year
Candian Rates
US Dollar Rates
British Pound Rates
1/1/2014
1
2014
1
10%
10%
10%
1/2/2014
1
2014
1
10%
10%
10%
1/3/2014
1
2014
1
10%
10%
10%
1/4/2014
1
2014
1
10%
10%
10%
1/5/2014
1
2014
1
10%
10%
10%
1/6/2014
1
2014
2
20%
20%
20%
1/7/2014
1
2014
2
20%
20%
20%
1/8/2014
1
2014
2
20%
20%
20%
1/9/2014
1
2014
2
20%
20%
20%
1/10/2014
1
2014
2
20%
20%
20%
1/11/2014
1
2014
2
20%
20%
20%
1/12/2014
1
2014
2
20%
20%
20%
1/13/2014
1
2014
3
30%
30%
30%
1/14/2014
1
2014
3
30%
30%
30%
1/15/2014
1
2014
3
30%
30%
30%
1/16/2014
1
2014
3
30%
30%
30%
1/17/2014
1
2014
3
30%
30%
30%
1/18/2014
1
2014
3
30%
30%
30%
1/19/2014
1
2014
3
30%
30%
30%
1/20/2014
1
2014
4
40%
40%
40%
1/21/2014
1
2014
4
40%
40%
40%
1/22/2014
1
2014
4
40%
40%
40%
1/23/2014
1
2014
4
40%
40%
40%
1/24/2014
1
2014
4
40%
40%
40%
1/25/2014
1
2014
4
40%
40%
40%
1/26/2014
1
2014
4
40%
40%
40%
1/27/2014
2
2014
5
50%
50%
50%
1/28/2014
2
2014
5
50%
50%
50%
1/29/2014
2
2014
5
50%
50%
50%
1/30/2014
2
2014
5
50%
50%
50%
1/31/2014
2
2014
5
50%
50%
50%
2/1/2014
2
2014
5
50%
50%
50%
2/2/2014
2
2014
5
50%
50%
50%
how do you set it up in access or excel so that when user enter certain rate, it gets populated for matching period and week number all across the dates the period and weeks fall into.
