Production Dashboard

[Wilson3]

Attached is a dashboard I use to monitor process center production data. Can anyone offer advice to make the dashboard show data weekly monthly and yearly without having to manipulate the data table. Thank you in advance for any advice and reccomendations.

 

[Luke M]

Hi Wilson,

First thing that comes to my mind is to build PivotTable(s) that use Table1 as the source. PivotTables can easily group dates however you want (week/month/year). You should be able to build the same charts still. You could even start adding some slicers if you want.

Also, are you looking for a formula-only solution, or is VBA acceptable?

 

[Wilson3]

Hi Wilson,

First thing that comes to my mind is to build PivotTable(s) that use Table1 as the source. PivotTables can easily group dates however you want (week/month/year). You should be able to build the same charts still. You could even start adding some slicers if you want.

Also, are you looking for a formula-only solution, or is VBA acceptable?

Thank you for your reply Luke. I have thought about pivots and the only reason why is my 80 year old father looks at these and he gets confused with pivots. I was hoping about a formula solution, since I am a VBA idiot!

 

[Luke M]

Ah, I understand. Not to worry, it can still be done.

Used some Named Ranges, and linked them to your existing charts. THere's a new worksheets which is where I put all my formulas that do the "heavy lifting" of figuring out new date spacing and summarizing the data. Make sure you take a look, as I'm not sure if you want to take an average, sum, etc. of your raw data. I did my best.

All charts now linked to orange cell on Dashboard. Does take a few seconds to recalculate, but it's not too bad.

 

[NARAYANK991]

Hi Wilson ,

I would like to caution you that summing up percentages is never a good idea.

The % Sys Down value for any period would actually need to be calculated as :

SUM of Sys Run Mins for that period divided by
SUM of Sys Mins for that period.

If you do this calculation for a month , say July , the % Sys Down value comes out to be 14 % rather than the 19 % that would result from a simple summation of the individual % Sys Down values for the month.

Narayan

 

[Wilson3]

Ah, I understand. Not to worry, it can still be done.

Used some Named Ranges, and linked them to your existing charts. THere's a new worksheets which is where I put all my formulas that do the "heavy lifting" of figuring out new date spacing and summarizing the data. Make sure you take a look, as I'm not sure if you want to take an average, sum, etc. of your raw data. I did my best.

All charts now linked to orange cell on Dashboard. Does take a few seconds to recalculate, but it's not too bad.

thanks for showing me this. I appreciate your work.

 

[Wilson3]

Hi Wilson ,

I would like to caution you that summing up percentages is never a good idea.

The % Sys Down value for any period would actually need to be calculated as :

SUM of Sys Run Mins for that period divided by
SUM of Sys Mins for that period.

If you do this calculation for a month , say July , the % Sys Down value comes out to be 14 % rather than the 19 % that would result from a simple summation of the individual % Sys Down values for the month.

Narayan

thanks for pointing that out. I will make the changes accordingly.