Hi all,
I've tried to search for the solution but was unable to find it, hence I hope you folks can help.
I have created a pivot table where I have two columns - Date Received and it contains dates when the item was received and then I have a column Date Completed and it contains the dates when item was completed. I need to create a graph that shows the trend of Date received vs Date Completed - a simple column bar chart. The graph needs to show Monthly Break down for each year i.e.:
2020
Jan - 5
Feb - 7
etc
2021
Jan - 10
Feb - 11
etc
Getting the count for 'Date Received' is straight forward - pull the Date Received field into Rows area, keep Years and then do same for Count in Values. This is where I get stuck, if I pull Date Completed count into Values, the count it shows does not show for the Date Completed dates, but for the Date Received date window. This is, i'm guessing, because Date Received is only field in Rows, I tried to move Date Completed to Rows, but that messes it all up and still shows unnecessary counts for Date Completed. If I try to move any of the fields into Column area then it won't give me monthly breakdown anymore but every single date break down - so table becomes huge.
I tried to create two separate pivot tables, which work fine and show data I need, except now I can't get a graph out of it because the pivot table fields are dynamic, they grow as new dates get added - Date received items won't always have completion date yet. I tried creating manual tables that reflect both pivot tables, but because they are dynamic - it didn't work, tried few other options still nothing. So now I am stuck.
All I need is a count for number of items for Date Received and Date Completed and then have a graph represent that.
Any help would be greatly appreciated!
Thank you
I've tried to search for the solution but was unable to find it, hence I hope you folks can help.
I have created a pivot table where I have two columns - Date Received and it contains dates when the item was received and then I have a column Date Completed and it contains the dates when item was completed. I need to create a graph that shows the trend of Date received vs Date Completed - a simple column bar chart. The graph needs to show Monthly Break down for each year i.e.:
2020
Jan - 5
Feb - 7
etc
2021
Jan - 10
Feb - 11
etc
Getting the count for 'Date Received' is straight forward - pull the Date Received field into Rows area, keep Years and then do same for Count in Values. This is where I get stuck, if I pull Date Completed count into Values, the count it shows does not show for the Date Completed dates, but for the Date Received date window. This is, i'm guessing, because Date Received is only field in Rows, I tried to move Date Completed to Rows, but that messes it all up and still shows unnecessary counts for Date Completed. If I try to move any of the fields into Column area then it won't give me monthly breakdown anymore but every single date break down - so table becomes huge.
I tried to create two separate pivot tables, which work fine and show data I need, except now I can't get a graph out of it because the pivot table fields are dynamic, they grow as new dates get added - Date received items won't always have completion date yet. I tried creating manual tables that reflect both pivot tables, but because they are dynamic - it didn't work, tried few other options still nothing. So now I am stuck.
All I need is a count for number of items for Date Received and Date Completed and then have a graph represent that.
Any help would be greatly appreciated!
Thank you
