Creating a widget

sahilhq

Hi,

I need assistance in creating a widget for a dashboard. So basically I have got a sheet with close to 2000 rows and each of those rows have action owners, due dates, etc. I would like to create a widget that would show when the actions are due and who the action owners are (action owners vs due date). Can anyone suggest the best way to plot this? Would be fantastic if you could suggest with the formulas that would be required to build the chart as well. Many thanks!

Andrée Starå

Hi Sahil,

I’d suggest using a report instead.

Would that work?

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

sahilhq

No, I don't want a report. A chart widget is what I would like.

Paul Newcome

So you are looking to have a list of owners and a count of how many tasks they have such as

John Doe: 12

Jane Smith: 15

sahilhq

@Paul Newcome So I have managed to create a widget that shows how many actions are due per action owner, overdue, open due within the next 2 weeks, etc (see attached). I would like to take this one step ahead by showing dates of when most actions go overdue. So if majority of actions will go overdue by end of March then I would like the dashboard to be able to show that.

Dashboard Widget.PNG

Paul Newcome

You would need to figure out the exact logic of how you want to calculate this date. Would you want the date that most fall on, or would you want the latest date, the last day of the month that the majority fall within, etc?

sahilhq

The dates that most of the actions go overdue would be better. Can we show multiple dates instead of just one?

sahilhq

FYI I have also attached a screenshot of what the sheet looks like so that we can start building up the required formulas and widgets from the data.

Format of sheet.PNG

Paul Newcome

Ok. So we want multiple dates that show when most of them become overdue. How many dates? Top 5? Top 10? Other?

sahilhq

Can we try top 10 please.

Paul Newcome

Ok. Let me try out a few things, and I will get back to you.

sahilhq

@Paul Newcome Hi Paul! Did you manage to figure out the solution? :)

Paul Newcome

@sahilhq My apologies. I got sidetracked, but this is back on my radar. Sorry about that.

Paul Newcome

Ok. So I have come up with a way to pull dates and counts. Below is a screenshot of the end result on a sheet. You would be able to pull it through a metrics widget to display on a dashboard.

In my example, I only used a total of 5 different dates. 6 of them had the same number of deliverables due. It did take a little work to set up (7 helper columns), but once it is setup, it should run smoothly at any scale as long as we are able to account for a few variables.

If the below screenshot looks good to you for the sheet data, we can get a few questions answered and start building it out for you.

sahilhq

That's awesome! Can you show me what the widget is looking like at the moment? and yes please guide me to start building this out!!

Paul Newcome

You could have it all on one. You could have each in it's own widget to space things out a little differently. There are actually quite a few different options depending on how your dashboard is set up and how creative you want to get with it.

So... On to the biggest question: What is the MAXIMUM number of unique dates you anticipate?

I know this is going to be a tough number to try to figure out, but my solution actually pulls all unique dates to start with. To do this, we need to "prep" the sheet with that many number of rows. If you want... Take the maximum number of rows total on a sheet. Depending on that number we can double it or increase it by 50%. This way you have plenty of flexibility.