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:
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:
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:
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
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 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-month | In progress | Customer Pending | Resolved | Closed |
| 2021-05 | 0 | 0 | 1 | 1 |
| 2021-06 | 0 | 1 | 2 | 3 |
| 2021-07 | 0 | 1 | 2 | 7 |
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 Number | Status | Priority | Severity | Date/Time Opened | Date/Time Closed | Year - 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
