How to create a report/chart of quantity over time?

Jordan1990

I have a sheet and report which has let's say candy types in the rows and ingredient units in the columns. Each candy type can have 1 ingredient unit associated with it, multiple ingredient units associated with it, or none at all. There is also a single date column that confirms when each candy type will complete production.

What I am looking to do is chart out the demand (quantity) of the ingredient units over time based on the production complete date value entered for each candy type. I would also want the ingredient unit name to be displayed on the chart in the Series legend.

At the moment, I have successfully set up a report which gives me the overall totals of ingredient units which also gives me a nice pretty chart on the dashboard, but I can't figure out for the life of me how to get this to forecast over time, say on a monthly interval for this year and next year.

Do I need to move the ingredient units out of the columns? Or is there an easy way where I can use this same totals report to give me a chart over time?

James Keuning

I think you need to move the ingredients out of the columns. Are you familiar with short and fat data vs skinny and long data? I think you are using short and fat, when you should be using skinny and long.

So I think you need to organize your data skinny and long, and then create another sheet that rolls up ingredients according to when they will be required. So if you have have two types of candy in production at the same time that use a common ingredient, you'll be able to pull out those common ingredients based on the production date.