MaartenBurg
New Member
- Joined
- Dec 13, 2016
- Messages
- 1
Hello,
I am trying to make a dashboard for one of my collegues which will determine if so called 'LS' and 'FS' deliverables are reviewed and delivered in time. Those deliverables have to be made for about 30 different customers.
I already made the sheet where they fill in the due date and when they've actually delivered. A formula determines what the status is. This can be: N/A, Waiting, Due, Late, Delivered in time, Delivered late.
The first column contains the name of the unique customer.
What I want to make is a pivot table that shows the count of the status for each deliverable, separated for the four deliverables, in one pivot chart.
Basically if I put the 'LS delivery status' in column, Count of entity in values, it shows what I want to see, but I want to see this graph for all statusses in one chart. I want to keep it a pivot chart so that my collegue can drill-down on the data by double-clicking.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Value:
Entity
[/TD]
[TD]Count of LS delivery status
[/TD]
[TD]Count of LS review status
[/TD]
[TD]Count of FS delivery status
[/TD]
[TD]Count of FS review status
[/TD]
[/TR]
[TR]
[TD]N/A
[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Waiting
[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]Due
[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]11[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Late
[/TD]
[TD]11[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Delivered in time
[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Delivered late
[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]5[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
Problem is that when I just put it in a pivottable, it groups up the different statusses. For instance: Delivered in Time-Due-Waiting- Waiting or Delivered late - Late - Waiting - N/A and counts these as unique.
This messes up my data and makes me unable to show what I want.
I have an example file, but couldn't upload it here so I put it on DropBox: https://www.dropbox.com/s/rkie0ut7dsgkcsu/Example file.xlsx?dl=0
I have tried searching the internet for a solution but did not find my answer. Sorry for the wall of text but I hope I made my problem clear. I am using Excel 2016.
If anyone can help me out, it is very much appreciated!!!
Thanks.
Maarten
I am trying to make a dashboard for one of my collegues which will determine if so called 'LS' and 'FS' deliverables are reviewed and delivered in time. Those deliverables have to be made for about 30 different customers.
I already made the sheet where they fill in the due date and when they've actually delivered. A formula determines what the status is. This can be: N/A, Waiting, Due, Late, Delivered in time, Delivered late.
The first column contains the name of the unique customer.
What I want to make is a pivot table that shows the count of the status for each deliverable, separated for the four deliverables, in one pivot chart.
Basically if I put the 'LS delivery status' in column, Count of entity in values, it shows what I want to see, but I want to see this graph for all statusses in one chart. I want to keep it a pivot chart so that my collegue can drill-down on the data by double-clicking.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Value:
Entity
[/TD]
[TD]Count of LS delivery status
[/TD]
[TD]Count of LS review status
[/TD]
[TD]Count of FS delivery status
[/TD]
[TD]Count of FS review status
[/TD]
[/TR]
[TR]
[TD]N/A
[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Waiting
[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]Due
[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]11[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Late
[/TD]
[TD]11[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Delivered in time
[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Delivered late
[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]5[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
Problem is that when I just put it in a pivottable, it groups up the different statusses. For instance: Delivered in Time-Due-Waiting- Waiting or Delivered late - Late - Waiting - N/A and counts these as unique.
This messes up my data and makes me unable to show what I want.
I have an example file, but couldn't upload it here so I put it on DropBox: https://www.dropbox.com/s/rkie0ut7dsgkcsu/Example file.xlsx?dl=0
I have tried searching the internet for a solution but did not find my answer. Sorry for the wall of text but I hope I made my problem clear. I am using Excel 2016.
If anyone can help me out, it is very much appreciated!!!
Thanks.
Maarten