Need to create a "heat-mapped" calendar based on previous years data for planned OOO.

[shukero]

Hi everyone,

I'm trying to create a color coordinated (heat mapped) calendar based on 3 years of previous data which will help my team members choose the "less intensive" days to take their OOO.

I have my data scrubbed into days, months, and years for all 3 years but I don't know how to create a "visual calendar" that's color coordinated to serve as the "yes this day should be fine to take off" (green), "This might be a dicey day to take days off (Orange), or "We will be busy! Don't take today off" (Red). Can someone help?

I'm hoping to put this calendar on "Sheet2" and all of my data is in "Sheet1" separated by the following:

date - 2016 - 2017 - 2018
day - # of jobs that day - # of jobs that day - # of jobs that day
day - # of jobs that day - # of jobs that day - # of jobs that day
day - # of jobs that day - # of jobs that day - # of jobs that day
day - # of jobs that day - # of jobs that day - # of jobs that day

Can someone help me make this?

Thanks!
~Mike

 

[Col Delane]

re: Need to create a "heat-mapped" calendar based on previous years data for planned OOO.

Hi Mike

A heap map (implemented via Conditional Formatting the relevant cells) needs a defined range of values (jobs) for each colour bucket. For example:

• "yes this day should be fine to take off" (green): =< 20
• "This might be a dicey day to take days off (Orange): 20 to 50
• "We will be busy! Don't take today off" (Red): 51+

You specify the threshold values when setting up the Conditional Formatting rule.

The following questions may not be critical in order to derive a solution, but will help me better understand the task ...

Q1. I'm not clear about how you intend to lay out Sheet1 and Sheet2 - could you please expand on this?

Q2. On Sheet 1, are Date, 2016, 2017, 2018 the column headings, under which will be rows holding the days in the year (i.e. 1 to 365/366)?

If so, the day No. and day of the week (Mon, Tue, etc.) will not line up from one year to the next (i.e. will shift by 1 day each year & 2 in leap years), so that may affect your job load calc's if the day of the week is a major driver of load.

​

Q3. How are you forecasting the work load in future dates based on previous data?

Q4. Would you not be better off with a single column (A) holding a continuous timeline of sequential dates; with one column (B) for Actual data; and a column (C) for Forecast load based on actual load in previous days (as per answer to Q3), where the formula in C only returns a value if the date is forward of today (i.e. returns zero or "-" for each past date)?

 

[shukero]

Hi Col,

PLease see my answers below.

Hi Mike

A heap map (implemented via Conditional Formatting the relevant cells) needs a defined range of values (jobs) for each colour bucket. For example:

• "yes this day should be fine to take off" (green): =< 20
• "This might be a dicey day to take days off (Orange): 20 to 50
• "We will be busy! Don't take today off" (Red): 51+

You specify the threshold values when setting up the Conditional Formatting rule.

The following questions may not be critical in order to derive a solution, but will help me better understand the task ...

Q1. I'm not clear about how you intend to lay out Sheet1 and Sheet2 - could you please expand on this?

A1. I'm using sheet1 (named "Tickets_Counts&Data) as the "database" / "data sheet" where all of the data is being kept which will be hidden for the end user - please see below for a visual on how the column is layed out. I want to use Sheet2 (named Calendar) as a "visual" auto updating calendar which will show the following:

no fill - If less than 10 tickets
green fill - If less than 30 tickets
yellow fill - If less than 60 tickets
red fill - If more than 61 tickets

Q2. On Sheet 1, are Date, 2016, 2017, 2018 the column headings, under which will be rows holding the days in the year (i.e. 1 to 365/366)?

If so, the day No. and day of the week (Mon, Tue, etc.) will not line up from one year to the next (i.e. will shift by 1 day each year & 2 in leap years), so that may affect your job load calc's if the day of the week is a major driver of load.

​

A2. Please see below: (I'm not sure if Picture sharing is working so here is a link to a screenshot of how the columns are laid out: https://drive.google.com/open?id=1AR93wg9IuI3OousH_hMhFGoSWJgrz3Jr)

Q3. How are you forecasting the work load in future dates based on previous data?

A3. I want to forecast based on average number of tickets for the previous 3 years.

Q4. Would you not be better off with a single column (A) holding a continuous timeline of sequential dates; with one column (B) for Actual data; and a column (C) for Forecast load based on actual load in previous days (as per answer to Q3), where the formula in C only returns a value if the date is forward of today (i.e. returns zero or "-" for each past date)?

A4. Not quite sure I can visualize what you are stating above; but I have these separated out as I do because I'm also running a pivot table which shows trends / data based on Days / Months / Years within Pivot Charts within another tab for the managers to look at on a monthly basis.

Please let me know if you have any additional questions.

Thanks,
~Mike

 

[Col Delane]

Hi Mike

The thought behind my questions about your sheet layout is that rather than effectively separate tables for each year (as shown in your Tickets_Counts&Data snapshot) that you have just one database of (sequential) dates and tickets and have "helper" columns that will facilitate any deeper analysis you may require.

Pivot Tables work best when similar data (e.g. date, day, month, year, No. of tickets, region, volume, etc.) is in the SAME column/field. Accordingly, I'm thinking your Sheet1 database would be better laid out as follows:

• Date, Day No. (where Sun= 1, Mon=2 ...), Month (where Jan=1, Feb=2...), Year, Forecast Tickets, ACTUAL Tickets.

Note that Day No., Month, and Year (all being whole numbers to facilitate easy sorting) can all be derived by formula from the date with the WEEKDAY, MONTH, and YEAR functions respectively.

You may also consider the WEEKNUM function to return the week number.
(See Ron de Briun's webpage "Week numbers in Excel" @ https://www.rondebruin.nl/win/s8/win001.htm for info about this topic)

Pivot Tables allow you to "group" (summarise) raw dates in all sorts of ways (weeks, months, qtrs, years) - though it takes a little bit to understand how to do this effectively!


I couldn't view the pictures, but could open the Google Drive file, though I could only see the layout of Tickets_Counts&Data. As such, I have no understanding of how your forecast Calendar (and therefore "heat map") is laid out.

1. Is it in typical calendar style of rows for each week?
2. How far into the future does it extend?
3. Is it a rolling future period forward of the last date populated with actual ticket volumes, or a fixed period (e.g. next week/month)?

As touched on in my first post, you need to decide whether you'll forecast ticket volume according to:

• day of the year, as it appears from the layout of your current database (in which case the day No. and day of the week (Mon, Tue, etc.) will not line up from one year to the next (i.e. will shift by 1 day each year & 2 in leap years), or
• week of the year (1-52) and day of the week (1-7) as this would better reflect seasonality and variations during the week due to work days and weekends (of course if such variations are applicable to your business)

The layout I've suggested above can assist with the above.