• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How should I best organize the data layer for dashboarding?

tweakin

New Member
Hi People,


I'm starting the process of building a dashboard for my management. It's a sales function with about 20 people across three teams, and about 10-12 products to track weekly totals of


I have found that the data that exists (that I need to draw upon) is organized in an odd way (on different tabs of excel, with one tab for each month. This means that some months' tabs have 5 weeks of sales while others only have 4.. This in turn means, of course, that I can't just use the INDIRECT function to get past the tab constraint, unless I use some pretty crazy IFERROR functions which would make the strings iteratively longer and would mean that the spreadsheet is finite rather than being able to refer to the actual current data. I'm reluctant to do this since there must be an easier way, and I really want to use the automatic date filtering function of excel from next FY, so I'd like to redesign the format in which that data is entered & stored.


I'm thinking the best way is to have each weekly total of each product sold by each sales person on a new row in the one worksheet.


That way I can have pivot tables and VLOOOKUPS in an analysis layer as staging tables for the display layer.


Would you guys think I am on the right track with this thinking?


Can you see any possible issues with having it formatted as such:


(Column A) Week Ending Date | Robert | Product1 $ | Product2 $ | Product3 $

(Column A) Week Ending Date | James | Product1 $ | Product2 $ | Product3 $

(Column A) Week Ending Date | Albert | Product1 $ | Product2 $ | Product3 $

(Column A) Week Ending Date | Peter | Product1 $ | Product2 $ | Product3 $ etc....


Or, would it be better to organize it like this:


(Column A) Week Ending Date | Robert | Product1 $ |

(Column A) Week Ending Date | Robert | Product2 $ |

(Column A) Week Ending Date | Robert | Product3 $ |

(Column A) Week Ending Date | James | Product1 $ |

(Column A) Week Ending Date | James | Product2 $ |

(Column A) Week Ending Date | James | Product3 $ |

(Column A) Week Ending Date | Albert | Product1 $ |

(Column A) Week Ending Date | Albert | Product2 $ |

(Column A) Week Ending Date | Albert | Product3 $ | etc....


Just wondering whether a) this will do? or b) whether either would be advantageous or give me headaches.


Thanks! :)


-m.
 
Generally you plan what you want on your dashboard

That will determine what queries you need to make from your data sources/s

Often people will use an intermediate sheet which contains several extracted summaries of the data source, each suited to a different part of the dashboard.


Once you are proficient at Named Ranges you can do a lot of this summary work using named ranges and hence avoid the summary sheet.


Chandoo has a good example of the original technique at:

http://chandoo.org/wp/2011/01/14/birthday-gift-hui/

File: http://img.chandoo.org/d/hui-birthday-dashboard-v3-b.zip


and my dashboard at

http://chandoo.org/wp/2011/02/25/excel-age-survey-results/

File: http://img.chandoo.org/d/My-Excel-Age-hui.xlsb

also uses this technique as well as a few named ranges
 
Hey Hui,


Thanks for the reply! :)


The fortunate thing, is that my management have been very clear about what they want on the dashboard:


Sales by (20x) sales-person by (weekly) date by (12x) products


The problem is that the existing data set doesn't contain all the necessary dimensions.. In fact, the data are scattered around the place in different systems, so I will be consolidating them together, and just need to confirm I'm on the right track with how to have them organized.


I'm confident that once they're organized properly, the staging of data for various parts of the dashboard will be the easy part.


Cheers! :)
 
Back
Top