• 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.

Multi-year dashboards?

B

Bthis

New Member
I am tracking the proposals we submit over time, with data starting in 2011. My data table describes each proposal in a row, including the submission date among many other details. I need to create a dashboard, preferably with slicers, to graph the number per month and display it by year, multi-year trends, and other aspects such as per author, per client type, etc. I'm trying to use pivot reports, which I have just discovered. But when I group by month, quarter, etc., it combines all the years. How can I accomplish this? Are pivot tables the wrong approach?

I am a Word guy who's amazed at what Excel does, but trying to understand how it does it is frustrating!
 
If you have field that tracks dates (in date value), then put that into either Row Label or Column. Excel will automatically group it by Year, Quarter and Month.

See attached example.

It may be different in older versions. What version do you use?
 

Attachments

Thank you again, Chihiro my hero! Sorry, I meant to include that: Excel 2010, from Office 2010 Professional. But when I create my Pivot Table from my data, it only contains the fields actually in my table, no extra date fields like year, quarter or month. Did you do something to create them?
 
This is fantastic, Chihiro! I added helper columns and hid them, and put the index for calculating quarters on the hidden sheet that also contains the holiday dates for my company.
 
Now I have a challenge creating the diagrams for the dashboard. I want to graph volumes of proposals by year(s), by author, and other criteria. The geographies are separate columns: US, EU, Canada, AsiaPacific, and Other, with an X in each row to indicate the geo(s) for that proposal. (If I didn't have proposals for multiple geos, I would just have one column with US, EU, Can, etc.)

I tried to use slicers to select which geos to display. That creates separate windows for each geo with options for "X" or "blank" in each window. Not pretty. How should I set this up?
 
Here's an extract with dummy data. I included
  • a dummy Dashboard showing what I'm trying to accomplish (subject to revision once I figure out what I can do),
  • a pivot table,
  • a worksheet of sample data,
  • a 'Holidays' worksheet with tables for calculating our holidays schedule as well as the lookup table for Quarters you showed me.
It also contains your example you sent me before. I am trying to track the number of proposals we receive ov
 

Attachments

Ok, your data structure isn't well suited to pivot table or dashboard reporting.

Give me couple of days to find time to organize it into more suitable structure.
 
Wow, Chihiro, that would be great! My original data structure consists of columns A-G plus P. I have this from mid-2011 on, in multiple sheets for different proposal types. The rest of the columns contain new information I'd like to track in the future. I plan to go back and fill it these new columns in for 2015 to create year-over-year comparisons for 2016 on.

I combined the multiple sheets into a single data set. I was adding single columns, with codes for the platform, services, etc. But there are proposals with multiple platforms, multiple services, etc. For example, B2B and DTC I used B and C, but some proposals are for both B & C. So I converted them to separate columns.

Thank you for any direction that will give me better tracking and reporting. Maybe I am being too aggressive, tracking too many things?
 
Hmm, you've got a lot of blanks in your data. Which makes it bit difficult to report on.

See how I combined Geos (also for Platform) into single column. This will allow you to slice and dice based on those info.

See Sheet2 for sample pivot.

I generally don't use Slicers as my company has users with older versions of Excel (up to 2003). So I only use it on presentations (for easy navigation).
 

Attachments

Thank you so much, Chihiro! I had separated Geos because often a proposal covers multiple Geos, such as US & Canada or Europe & AsiaPacific. Since there are so few, I guess I could create each of the potential combinations--that's 25 potential combinations. That would be much more complex for all 14 services, though. And then I'd want to see all the proposals for any combination, including all that covered Europe only, or all that included Europe and anywhere else. Maybe I need to back off on my expectations.

Since this is internal only, and we standardize on Office Pro 2010, I don't have to worry about users on other platforms.

Thank you so much for your help!
 
You must log in or register to reply here.
Back
Top