Forum Discussion
Creating a fill in table for gaps
I am trying to create a table, with continuous calendar dates, showing the total number of tickets opened per day per associate. Sometimes a specific associate will not open a ticket for or day or even several days. I have the raw data for each ticket opened by an associate per day but I do not know how to create a table, based on the raw table, that fills in the gaps (puts a 0 as the count for that day for that associate) when an associate does not open a ticket. Below is a simple version of my raw table called “INC Tickets.” Any suggestions? Associates can come/go on the team so would prefer not to try to hard code names into this, if possible.
| Ticket# | Opened Date | Opened By |
| 1000 | 9/1/2018 | Ann |
| 1001 | 9/1/2018 | Ann |
| 1002 | 9/3/2018 | Brian |
| 1003 | 9/4/2018 | Dan |
| 1004 | 9/4/2018 | Brian |
| 1005 | 9/4/2018 | Dan |
| 1006 | 9/4/2018 | Dan |
| 1007 | 9/15/2018 | Cathy |
| 1008 | 9/15/2018 | Cathy |
| 1009 | 9/20/2018 | Cathy |
With a proper date calendar table 'dCalendar' you could take 'dCalendar' [Date] into a table visual and choose Show items with no data like the below image.
Maybe a simple measure like
# Open Tickets = IF( ISEMPTY('INC Tickets'), 0, COUNTROWS('INC Tickets') )will yield :
but this seems rather difficult to read and if I actually had a full calendar the scroll would last forever.
Putting into a Matrix visual may be a better option?:
4 Replies
With a proper date calendar table 'dCalendar' you could take 'dCalendar' [Date] into a table visual and choose Show items with no data like the below image.
Maybe a simple measure like
# Open Tickets = IF( ISEMPTY('INC Tickets'), 0, COUNTROWS('INC Tickets') )will yield :
but this seems rather difficult to read and if I actually had a full calendar the scroll would last forever.
Putting into a Matrix visual may be a better option?:
Hello ChrisMendoza,
I am very sorry for the late response but I want to thank you very much for your response. It seems to work but I guess I do not understand how your simple measure actually works. How does it know to key off of "Opened By," especially if I had more columns that it should not key off of, to fill in the gaps?
Thanks Again
By using the relationship between your 'INC Tickets' table and the 'Calendar' table I am able to ask the question "Is there a transaction that occured on this 'Calendar'[Date]?" If there is not, then the value is zero. Otherwise count the number of rows that occur on this 'Calendar'[Date].
At this point, we're just counting rows, regardless of names (your request to not hard code these values) where transactions are occurring. Since you actually want to see who had transactions you put the filter context [Opened By] into the visual.
Since you didn't have a desired output, I created the easiest to show what you were asking for.
Hope that helps in your understanding.
