I am trying to create a dashboard that projects a per-person quantity of items that meet a criteria vs. a quantity of items that don’t meet the criteria.
The criteria being that two separate date columns have values.
I was easily able to create a dashboard that shows how many items are assigned to our team members. So let’s say Justin has 17. However, only 15 have two dates filled in. I want a dashboard (stacked bar chart preferably) that shows 15 and 2.
When I wasn’t able to achieve this in dashboards, I tried to write some formulas. Then I could use the formula output to create a status and when that status was met, set up an automation for the item to be regrouped. That would allow me to display easily using the group in my x-axis.
Unfortunately, I can’t figure out this formula. Does anyone have any guidance for me? Much appreciated!
To check whether one, two, or no date columns are filled on an item, you can use this formula:
IF(AND({Date 1}="",{Date 2}<>""), 1, IF(AND({Date 1}<>"",{Date 2}=""), 1, IF(AND({Date 1}<>"",{Date 2}<>""), 2, IF(AND({Date 1}="",{Date 2}=""), 0, “”))))
You would need to replace the “Date 1” and “Date 2” portions with the exact names of the date columns you are working with, but this is what it would then look like on your board:
IF you’re pulling this information then into a Dashboard, you will need to make sure this formula is being used on every board you’re pulling data from. You can then create a stacked bar chart that shows how many items have 2 dates filled, 1 date filled, or no dates filled and stack by person:
Your Chart Widget settings would be the following to show the above:
Chart type: Stacked Bar
X Axis: People
Stack By: Formula
Y Axis: Count Items
Would this work for you? If not, could you elaborate more on what you are specifically trying to achieve? We can go from there once I know more about what you’re trying to build!
I hope this helps! Remember to mark as solved if this answers your question
