Creating a widget

sahilhq

Honestly, It wouldn't be too difficult to figure out the maximum number of unique dates had I known the correct dates at the moment. The dates will be supplied to us by the clients team in a few days time and that's when I will have a better idea. At the moment I am assuming that we will have a maximum of 10-15 unique dates. One thing that happens quite a lot on the project is that dates start getting re-forecasted. So say in the beginning we have 13 unique dates, there is a great chance that this could go up to 30. Do you think the formula sheet at the back will be able to cope up with this change automatically or will I need to make manual updates in the future?

Also, for the time being can you assume 13 unique dates and I start building up the dashboard based upon that assumption?

Paul Newcome

Since a new sheet automatically starts with 50 rows, I will go ahead and make the maximum 50 unique dates. This is automated pulling from the project plan, so anytime a date is changed, it will automatically update everything and be reflected throughout. With this initial setup, you will not need to update anything unless you have more than 50 UNIQUE dates. Even if you have 2,000 rows on the project plan, as long as there are only 50 different dates (used multiple times) on those rows, this initial setup will still work and maintain its automations. I will also go over expanding after we get the initial setup completed.

So first things first... Is this going to be built on a sheet separate from the source data, or are you going to be using extra columns on the same sheet?

sahilhq

Can we please use a sheet separate from the source data?

Paul Newcome

Here is a mock-up of the metrics sheet. First three rows are informational plus a buffer row before the actual sheet starts on row 5. The last two columns on the right are the two you would use for your dashboard.

This can potentially pull more than ten dates. In my example it pulls 12. Here is why...

It is actually pulling the top ten COUNTS. If there are multiple dates with the same count, then it will display those dates next to the count. This way nothing gets lost.

So if you had 1 Feb., 2 Feb., and 3 Feb. all with a count of 15, you would see a single count of 15 with all three dates listed next to it.

Below is a screenshot of 2 examples of how you can display the data on a dashboard. Of course there are MANY other options though.

sahilhq

That's really amazing! I am going to try to replicate what you have created for a dummy set of data and see how I get on with it. I really appreciate your help, Paul!

I was just wondering, in order to make the dashboard more interactive and give the dates more context do you think it will be possible to somehow show who the action owners are for all the set of dates? So using your data, say all the 10 counts for 01/06/20 are actions assigned to me, can we have my name next to the date (01/06/20 - Sahil Haque) or my name where the blue bar is?

Paul Newcome

It is possible, but the exact solution would depend on exactly how you wanted it displayed.

We won't be able to have it placed on top of the blue bar itself, but (with some testing/experimenting) we may be able to use a stacked bar graph and have it as the dates for the labels and then different colors in the legend for each person.

The easiest way though would be to use a similar approach to the dates and join the names together and include them in some metrics widgets.

sahilhq

Ok, brilliant. I'll get back to you soon with what exactly I would like to displayed. Thanks.

sahilhq

@Paul Newcome Hi Paul, can you please give me view access to the sheet that's linked to the Ranking/Counts sheet that you created? Also, can you guide me to what [List/Rank]@row, "") in the formula =IFERROR(LARGE(DISTINCT({Due Dates Sheet Due Date Column}), [List/Rank]@row), "") means? I am getting a #UNPARSEABLE error and I think it is do something with the way I am inserting the second half of the formula.

Paul Newcome

You should be able to copy/paste from the published view and just change the formulas to match your column names as needed.

The [List/Rank]@row reference is actually incorrect. I had adjusted the solution after putting the formula in row two and forgot to update it. My apologies. The correct formulas are now displayed in the published version.