Reporting on Work Distribution in ClickUp's Hierarchy using Dashboards + Formulas

I spend a lot of time on this channel talking about how work management software like ClickUp is super versatile. And it is. You can set up that hierarchy in just about any way. But when it comes to matching a software to the way you do business, there can be a bit of a learning curve figuring out how to use the hierarchy, how to structure your fields and how to get the reporting in the way that your brain actually works. Now to help you on that journey as you're trying to do that translation, I'm going to be taking you behind the scenes today. We're going to be going into the Weekly Review, which is the live show we do for paying clients only, inside the process driven universe. Inside this live stream I'm handling about a dozen questions every week and giving my strategic advice around ClickUp process and everything in between. And the excerpt I'm going to share with you today is a conversation we had around this very topic. Figuring out how to help someone who's managing the processing of loans distribute out tasks to their brokers and manage the workload of that team. Now if you are someone who's a real estate professional or a financial professional, keep your ears tuned. Posted. I don't know what the verb is, but that. But even if you're not in the real estate space, stay tuned because this conversation is going to touch on things like capacity, workflow management and reporting in a few creative ways of the ClickUp hierarchy that I think any service based business is going to benefit from. With that, let's take you for a sneak peek inside the Weekly Review.

The next question we'll dive into is how can I see the whole company in a dashboard widget? So a little bit of context. We process mortgage loans. I need to monitor the number of loans that each processor has and reroute loan tasks. Each processor has an office space and each broker they work with is a folder with the loan tasks specific to that broker.

So let's just say this is my loan and this is my task. And then these lists may not exist or it just might be all loans or something like that. I might be close. I hope I'm close enough. I'm going to take this and I'm actually just going to humor me here as I do a quick snip so I can have this on my sidebar and we're going to recreate this enough so that way we can build this out with you. Tracy. I'm going to be honest, it's probably not going to be perfect, but we'll get close and it should give you enough to be able to move forward. So here we're going to reutilize this space and we're going to call this Tracy's offices as space. And then our folder is going to be our broker. So what do we want to call our broker? Super broker A. And then the buddy broker. All of our brokers have great names. And then inside of these, we have our list of all loans. And inside this list, our loans are considered tasks, which I love that. Love that you're using some database, thinking you're not getting constricted by task equals a task. Loan number 1234, loan number 1235 and so on. Hope I'm getting close. And now back to the crux of the question. Now that we have this all mocked up here, we want to be able to visualize the whole company and all processors workloads in a dashboard view. When it comes to the processors and the people who are running each of the pieces, are the processors your assignees. I'm trying to figure out where that data is in ClickUp. Yes, I was hoping so. Jackpot. Assignees are our processors. So let's go ahead. We're going to invite Mariah into this space so we can go ahead. Or Monica. We'll do Monica. She'll be so confused, but it'll be perfect. I'm going to invite another human being so we can pretend we have some different processors on here. So our two processors. And I'll even put a note up here for good documentation to say on this list, processor equals assignee. Anything else we are translating, we want to make sure we say so. Task name equals loan id. We want to make sure we have those clear documentation because ClickUp does not yet let us adjust those values. Once we have this in here, we can start reporting on it. And how do we do that? Well, let's head into dashboards and look at different ways to break down our data. The data we can report on is generally just the data attached to the task. That's our little issue in this setup. Right now we've got things broken out by broker in the vertical hierarchy and office in the vertical hierarchy. Those details are challenging to report on in dashboard widgets without just creating multiple copies. So I'll show you what that looks like. Let's go in here. We're going to create a processors dashboard, own distribution, whatever we'll call that. I see a lot of people go in here and they go to featured. It's almost never going to serve you best. Just as a Little pro tip here. You're almost always going to be better off going into custom because with custom you can create almost everything in these other ones. But if you start with featured like this one, you're going to get pigeonholed and not get all the features. So a little pro tip there. I'm thinking we want to see changes over time, not just in one moment. So I'm thinking bar chart again where my bias is going. And we are going to report on the whole office and we're going to call this office a distribution. Our X axis is going to be time once again, I think at this point, because we want to be able to see how it changes over time and how it is in certain days and weeks. And our Y axis is going to be the number of tasks. In this case, we want to group it by the processor. So in this case, that would be the assignee to see how many tasks are assigned to each processor at each moment in time. We can either break it down by day, by week, by whatever unit you want. If you want to see this here, make sure you include subtasks, if those apply to you, and add that in. What this is going to show us is how many loans each processor has each day, week, whatever unit we select. We could flip this in so many different ways if we wanted to, though. So if we didn't care about the changes over time and we just want to zoom in on a moment, we could do that. But this is going to show us the total. So that bar is going to go up as we have four loans the next week, five loans the next week, and so on. This is probably how I would go. And then I would duplicate this for each office. So I would do for my Office B space. There we go. I would go in here and I would select my B space, whichever one that is. Maybe it's this one. And then I would save the same settings and save it. So then I hit Office B, Office A, Office C, Office D. And this would give me a visual if I wanted to report on all offices everywhere. That's when I would start just selecting all of them. But just realize once I do that and I select all of my different offices, all the data is going to be smushed together. I can't break it out by office in my chart because of the limitations of ClickUp. That's why I'm giving you four widgets as opposed to one.

That's a good thing because I was just going to ask you if we could put them all together.

Yeah, we totally can. You'll see. Let's look at the result here. It's just going to show up. It'll show by a person and if the people are unique to the office, maybe that's enough. But for a lot of people, they want to see like office at a time. And so this is like here it's showing everything in every spot. And it could be a little overwhelming right now looking at each office and going into their total loans. Yeah, Tracy, if I was you, I'd probably do this view and just have three or four widgets. Assuming you have a small number of offices. If you have 50 offices, then maybe I would do this.

Layla, did you see Tracy's comment? I'm going to show it really quick.

Yeah. We want to make sure they are starting start date and closing due date within X days. So there's a few options we have in terms how to make that particular reporting requirement happen. And for folks that are currently doing it, what you're talking about right now, Tracy, of using columns, I would say that's like the starting point and very effective if you don't have a crazy high volume. But let's say we're at 100, right? And we want to just make sure that our 100 deliverables are being done in a timely manner. We could have our start date and due date visible. And then what we'd likely do is use a column to maybe also look at last update, because that's also going to tell me date updated. I don't know why. Half the time it's last update and half the time it's date updated. Come on. And then we'd use a formula. That's typically what I would be looking at. So I would be using a formula field, which is a bit of a dead end, but works on a per task basis of close versus close date versus due date. Now we'd be reporting on what's the difference? Did they close it early, did they close it late? And this is how I'd be evaluating performance. And in fact, this is something I'm using right now in our own video production area to check in on certain team members who have personal goals of trying to finish things before their due dates. I was like, hey, we can do this. It's just a formula. What that looks like is the days between the due date and the close date and it'll report that number here. So let me just go ahead and put a little string at the end. And here we go. So the due date, let's just say is tomorrow. I might have put these in the wrong order. We'll see in a second. If I close it right now and I show close to make sure it doesn't go away. It was closed one day early. I really. If I want to be correct, it'd be day parenthesis s early. But this is what I would be using. I would have this as a view on the overall, maybe everything, maybe the space. And I'd probably just be, you know, I'd be the weird person who would have it just as my own view. And I would call it something like early close monitoring. Now, we can't sort or report on this metric, but again, if you only have 100 loans, we're not going to group by list. We're going to view them all side by side by side. And we're just going to scroll down this column and if we see anything where it's negative, meaning they closed it after the due date. So for example, this one was due on the 9th. I'm closing it today. What am I doing? Resolve all items. We'll see. Negative one days early. That's when we want to start keeping an eye on that, having a conversation, noticing. If we wanted to make it more obvious, we could even have it be if it's a negative number, show a red emoji. I mean, basic Excel formulas work great here. So that's how I would go about it in terms of the per task basis. If you want to report on it on a macro level, there's some widgets we could do. But I might have already went down too far of a rabbit hole on this, so I'll stop here and check in.

That is so cool. Okay. I don't know about anyone else, but formulas in ClickUp kick my butt every time I attempt a formula. Granted, I love Excel, I love Google sheets, but ClickUp formulas like kick my butt. So the fact I don't know that is awesome.

Yeah. What the weird part about ClickUp formulas and we have a formula gallery, by the way, for anyone who, like me too, finds ClickUp formulas a little like they don't quite work the way you think, which they don't. We have a formula gallery in the membership for members where it goes through, I think like 10 or 15 examples of formulas and how the formulas work in order to, like, help you break down the logic of why they work the way they do. Yeah. And just keep in mind with formulas, if I were to say, copy and paste the formula I just put in the chat and paste it in here, it may not always work. So when it comes to formulas, as silly as it sounds, take the time to retype it, manually clicking on variables as you go, and it'll almost always work. So due date, click on that rather than typing it out. It's a strange clickupism. I love the real estate industry. I don't know why. So geek out about this a little bit more than I should, but generally you'll have a closing. Good, better, best. So you might have like 20 days. Oh my God, we're amazing. 45 days. Yeah, we're good. More than 45 days. We're in trouble. Know that you can use if functions nested. You can't get too much fancier and ClickUp, but you can use nested if functions to have emojis or different things show up based on that. So you could say, you know, if the value is greater than 30, then red emoji, fire emoji if the value is less than 20, like hugs emoji. The reason I say emojis despite them being a little bit Internet culture Y is because they pop out. And since we can't do conditional formatting, it is great to. It's great to make sure that you have a visual indicator to make up for the fact that we don't have conditional formatting. And for anyone who's like, what the heck are you talking about? Formula Gallery. Formula Gallery. Formula Gallery. It has these in there. Just copy them. You don't need to come up with them from scratch. Yes, great. Less than 21. We cracked the whip. Exactly. Or greater than 21, whatever it is. Yeah, that's a lot of folks operate that way, so it might be nice to line up your formulas with those units.

Okay. And I am attempting to look for the Formula Gallery and then I'll link it in the chat.

So that concludes our little preview of the weekly review, which is the weekly live stream we're doing here at ProcessDriven just for our clients every single week to keep them up to date on what's happening. ClickUp's world and tackle a ton of Q and A. If you're someone who would like to spend a little bit less time googling and more time just getting the answer for from an expert, consider joining us inside the processdriven membership. Despite the weekly review being amazing, it is just one of many benefits and support avenues we offer for our clients to help them get their systems and processes streamlined and inside their work management software. If that sounds like something that might be helpful to you, save you a little bit of time, some gray hair and stress. Please do check out the info in the description below. Otherwise, I hope you enjoyed this sneak peek and until next time, enjoy the process.