Hi there,
Looking for advice on how best to approach charting what is essentially text-based data. In the attached file you will see two simple lists:
Rows 1-9 have the original data set. The values in column A are vehicle references, the data under the rest of the columns, (dates), are text values representing what the truck was doing on that date; i.e. "F" truck is in the field, "S" truck is in the shop, "D" truck is down, (in repair).
Rows 13-21 the same data but with the letters replaced with values, "F" with a "2", "S" with a "1", "D" with a "0". This was my attempt to get something I could chart but it looks ugly no matter how I manipulate it.
If anyone has advice on how I can chart this that would be great - I am keen to show the trend of each truck over time in each of the three categories.
Many thanks,
Alan
Hi Alan
I would use F=1, S=0 and D=-1 and then use the Win/Loss sparkline.
This will allow me to see the down/idle time, which I consider to be critical.
You can also use conditional formatting to color code each category.
Hope this helps.
Sunny
Hi Alan,
Thanks for sharing your file. First, you need to unpviot the data so that it's in an tabular format. You can use Power Query to unpivot.
Once you have the data in a tabular format you can use PivotTables to summarise it and visualise it in Pivot Charts. In the file attached I've used Power Query to unpivot the data, then inserted a PivotTable, Pivot Chart and a Slicer.
Hope that points you in the right direction.
Mynda
Great solutions guys, thank you both so much for the help!
