Calculating cumulative value on dates

Netanel yosef

Hi,

I have a sheet with a product that i produce and i have two column "in process" and "moved to stock" date type.

i want to see by graph the time that each product was in fabrication. for example:

item A :

in Process: 01 NOV 2020

Moved to stock: 18 NOV 2020

so basically, the item was in fabrication in WW45+ WW46 + WW47

so i want to see the nubmer ONE in this work week. and i have many items so i want to sh the cumulative value.

any idea ?

Sean Morgan

Hello @Netanel yosef

Are you able to provide screenshots (whilst hiding any sensitive data) of your Sheets so I can have a visual representation on how to create a matching Formula?

Furthermore, I'm unsure as to where the number 1 in the "Work week" fits into this solution.

Regards

Sean

Netanel yosef

yes here the screenshot

Genevieve P.

Hi @Netanel yosef

A Chart Widget in a Dashboard wouldn't be able to give you the type of graph view that you're looking to do with the dates as the X-Axis and the values mapped over time.

Instead, what about displaying this in a Calendar view, so you can see the Start Date ("In Process") & End date ("Moved to Storage") listed across a Calendar, would that work? It would only show the tasks that have both a start and end date, like so:

You can then use a Web Content Widget to display a Published view of this sheet in a Dashboard, if that's your end goal.

If you're looking to see how long this task has lasted across these weeks, you can use the WEEKNUMBER function to find the Weeknumber of the end date and minus the start date's weeknumber to count the weeks of the task:

=WEEKNUMBER([Moved to Storage]@row) - WEEKNUMBER([In Process]@row)

Now, I've updated this formula to only return the number of weeks if there's a date in that Moved to Storage column, like so:

=IF([Moved to Storage]@row <> "", WEEKNUMBER([Moved to Storage]@row) - WEEKNUMBER([In Process]@row), "")

Then I put it in a helper column, like so:

Is this what you were looking to do?

Cheers,

Genevieve

Netanel yosef

Hi @Genevieve P

Thanks you for answering!

this is a great solution but i am looking for something bit different.

Actually, i need to show a graph with the QTY of task per WW or month and not the duration for each task.

the main idea for that is beacuse i have a limit of 50 taks for type A and 100 for type B and i wnat to now immediatly when i cross the limit.

Netanel yosef

Hi @Genevieve P

Thanks you for answering!

this is a great solution but i am looking for somthins bit different.

Actually, i need to show a graph with the QTY of task per WW or month and not the duration for each task.

the main idea for that is beacuse i have a limit of 50 tasks from type A and 100 from type B and i want to know immediatly when i cross the limit.

Genevieve P.

Hi @Netanel yosef

Thank you for clarifying! Based on this, it sounds like you'll need to use a COUNTIFS formula to find these numbers. If you want this to be in a chart, I would suggest using a separate sheet to create these calculations and use Cross-Sheet references in your formulas.

A COUNTIFS function works like this:

=COUNTIFS({Column in other sheet}, "Criteria 1", {Next Column in other sheet}, "Criteria 2")

For example, you could first look for a specific task and then for a specific month:

=COUNTIFS({Task Column}, "Type A", {Date Column}, MONTH(@cell) = 1)

^ This count how many of your tasks are "Type A" and are in the MONTH 1, or January. Now, the MONTH function can sometimes give an error if there are blank cells, so you can avoid this by adding an IFERROR function around it.

Try this:

=COUNTIFS({Task Column}, "Type A", {Date Column}, IFERROR(MONTH(@cell),0) = 1)

Then to create the count for Task B, just change out the first criteria:

=COUNTIFS({Task Column}, "Type B", {Date Column}, IFERROR(MONTH(@cell),0) = 1)

Today's Month:

If you wanted the formula to only look for tasks which are in the current month, you can use the TODAY function instead of saying = 1 and specifying a month:

=COUNTIFS({Task Column}, "Type B", {Date Column}, IFERROR(MONTH(@cell),0) = MONTH(TODAY()))

Does this make sense? In order to actually build out your specific formula, we would need to know the columns you want to look into and the criteria you're looking for. Then once you have this data you can use the number to map into a chart widget.

Cheers!

Genevieve