Pivot chart: fixed legend with fixed colors

Melzebu

New Member
Joined
Jan 24, 2022
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Hi everyone,

I have a problem with a pivot chart and its legend.
I have a table coming from a query that is listing tickets. Those tickets have 4 different status types:
  • In progress
  • Customer Pending
  • Resolved
  • Closed
I created a pivot table out of that main table where values are shown as Running Total In so when making a chart out of it I see the growth of tickets.
I had to add to the main table a column called "Year-Month" where I'm basically converting the opening date of the ticket to only year-month in text (ex. ticket opened on 18/05/2021 would appear as 2021-05)
That way all the tickets opened in May 2021 are counted together according to their status.

For the record, I wanted to do that based on quarters but it keeps on adding Q1 of 2020 and 2021 together or it was separating years so the Running Total In was interrupted after each year (couldn't find a way to solve that in the first place so feel free to suggest something for that too, if you can).

Back to the chart topic: when I made the chart for the first time it had all the status types so I could assign a color to each data series and the legend showed it accordingly.
Unfortunately, when the query pulls out tickets with only one status type (closed for example), the chart's legend shows only the closed status with the wrong color.

I was thinking to overcome the issue by creating a table with all the status types so, even if there is no data for one status type, the chart would still show the legend without messing up the colors.
I'm not that skilled though to create a macro that would fill all the necessary rows (according to the main table length - which changes constantly) counting the number of tickets while showing the values as Running Total In.

Basically, the macro should check each row in the main table and report to the second table the value 0 (or better an empty cell) in the cell of the non corresponding status of the ticket and the correct value to the cell of the corresponding status and so on, populating the whole table while running the total in.
Looking basically like this:
Tickets in year-monthIn progressCustomer PendingResolvedClosed
2021-050011
2021-060123
2021-070127

Moreover, the count is made based on Year-Month - as I explained earlier - so, as shown in the above example table, if in June 2021 I had 2 closed tickets, summing it with the single one closed in May 2021 the count "running total in" goes to 3.

After this long and tedious explanation I ask:
Is there a way I can have a fixed legend with all 4 status types and their respective colors even when there is no data for a status type?
Or do you have any other idea, suggestion to fix this the easiest way?

If you think that the approach of a table with all status types would suit better my situation, I implore anyone to help me figure out how to write that macro.

The name of the main table is "Full_report" and the headers of the main table are the following, if that helps:
Case NumberStatusPrioritySeverityDate/Time OpenedDate/Time ClosedYear - Month

I hope someone out there is willing to help me with this.
In any case, I wish you all in advance a great weekend.


Cheers
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
piece of a cake if you give a link to a representative table
 

Forum statistics

Threads
1,181,081
Messages
5,927,970
Members
436,581
Latest member
Luceroso

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top