Trend analysis in a large dataset

[fabio.heck]

Hi all,

I've been reading for a while, but finally decided to make an account in the hopes that I can give back -- but for now I'm stuck with this headache and hope someone can help.

I have been working with a drinks outlet which sells alcohol bought from here, there and everywhere, because of this a lot of the products are of poor quality, short 'use by' dates etc. At the end of the week the EPOS software on the registers allows a weeks worth of sales and waste to be downloaded across all 40 outlets categorized by item. This will repeat across all 40 stores for around 2000 items, though this can fluctuate. Each row will present the outlet, item, profit and waste and repeat for each.

What I would like to do is to make a workbook which allows me to key in an item #, and I will be able to view a graph of both it's sales and waste with a trendline. I would also like it to display a table showing the sales and waste of that item across all stores over the previous four weeks, with each week represented on an individual row. I would also like it to be able to extract the ten worst performing items over a four week period and present them in a different table.

This seems like a big ask. What makes things harder is that I'm limited to Excel 2007 with no add ons which could make the data more manageable, and also help presenting. Also, what has been stopping me is the scale of the data. Each week generates around 80,000 rows. I've tried defining arrays, or even attempting to explore via a pivot table, but it's just become a headache.

I had originally thought a few SUMIFS would solve this, but it seems I'm in way over my head. I feel simply I would like to know if the above is even possible given the constraints? Or if this is a much bigger job than I could have imagined?

I have attached a sample file as I'm unable to take the actual with me and recreated the output from memory. This shows how the output represents one store, and the format it follows for items.

Apologies for the wall of text! Hope you guys can shed some light on this.

Best wishes,

Fabio

 

[Hui]

Fabio

Firstly, Welcome to the Chandoo.org Forums

Have you visited the Chandoo.org sections on dashbaord construction.
http://chandoo.org/wp/excel-dashboards/
http://chandoo.org/wp/excel-school/

I'd suggest you start there and pull a few examples apart
http://chandoo.org/wp/2014/06/12/state-migration-dashboard-contest-entries/

They all work similar
1. Data Store, You already have that
2. Extract tables of data via formlas, Pivot tables etc to meet your requirements
3. Present the data in charts, summaries etc
4. Add interactivity if you really want it

 

[fabio.heck]

Hi Hui, thanks for the welcome!

And thanks for the advice. I had started to pick apart one of the dashboards you linked and gave it a few days before replying so I actually had tried as much legwork as possible. I decided to start afresh after seeing the layout of one dashboard and started building the data in. From the raw data, I have it pulling 3x tables summing each of the groups to another table (9 tables actually, one for each area). I now have a table with 4 columns. Week, turnover, waste and waste as a % of turnover (as this will be easier visualised). It seems to be going to plan. Relatively. Although I've only really experimented with different sections rather than putting it all together, I'd much rather try to get the thing working first than attempt to fix it after!

I'm still having difficulty with one or two things, and I've found it almost impossible to find a resource (simply because I'm not sure what to search/keywords).

1) I cannot find a way to take out the top worst performing items from each area from the data. RANK doesn't seem to work (I'm not sure of the exact error, but its generic), and stepwise evaluation of the formula doesn't seem to yield any clues. I'm guessing the aim is to SUMIFS the entire table, and within that move them to another table. I've yet to give pivot tables a try, although I've had difficulty in the past getting them to auto-sum the new data when its pasted into the raw tab. (This may have been due to a vb script clearing down and deleting the array...) I may be able to fix this by adding a table, but 11 weeks of data is already at 800k rows and I'm worried it might start to perform slower than it already does.

2) From this guide to interactive tables ( http://tim.webanalyticsdemystified.com/?p=2176 ) I was able to use the "
Dashboard Tab Setup — Part 1" guide to return the current week using the =INDEX(Main_Data,MATCH. My aim was to provide a chart for each area, showing a user configurable 2,4,6,8 week snapshot with a trend. However, only for Table 1 (or table12 as it's listed in my workbook, which might illustrate the journey so far..).

I could do the same Report Range/Report Period drop down for each table, but this seems to be a rather bulky way to tackle this problem, also not user friendly at all. Surely there's a way to allow the user to select an "area" (one of the 9 area tables), then select the report range and report period auto-populating a single chart on the dashboard? I did read some of the literature on dependent tables, but I'm not sure this is what I'm looking for..

Again, thanks for your links and help! It's come a long way since then. If you get a free moment, it'd be great to hear any suggestions. If it would help for me to rebuild a simulation workbook, I'll post it up tomorrow. Once I've made notes of the format.

Thanks again,

Best wishes

Fabio.

Fabio

Firstly, Welcome to the Chandoo.org Forums

Have you visited the Chandoo.org sections on dashbaord construction.
http://chandoo.org/wp/excel-dashboards/
http://chandoo.org/wp/excel-school/

I'd suggest you start there and pull a few examples apart
http://chandoo.org/wp/2014/06/12/state-migration-dashboard-contest-entries/

They all work similar
1. Data Store, You already have that
2. Extract tables of data via formlas, Pivot tables etc to meet your requirements
3. Present the data in charts, summaries etc
4. Add interactivity if you really want it