Log In
Register
Home
November 28, 2020
Offline
Hi Everyone,
Hoping someone with a little more pivot table experience than I can assist here! I've been keeping a dashboard of weekly aircraft movements in Europe this year (relative to movements last year) and am having several problems. The main problem I'm having is having it update/ refresh without making manual updates as I'm using dates for column headings so end up having to update the columns using the Field settings every week with the new data. This is no big deal but the second problem linked to this is how I can make a dynamic/ moving calculation column based on the last 4 weeks movement (e.g. showing the movement in week x compared with week x-4) so i can see what countries have made the largest improvement in the last 4 weeks.
I've attached the sheet and the dashboard (in progress) and also highlighted the problems I'm having.
Any thoughts would be super helpful.
Thanks all!
Mike
See Answer
July 16, 2010
Offline
Hi Mike,
Welcome to our forum! The file isn't attached. I suspect you didn't clikc the 'Start Upload' button after selecting the file.
Anyhow, I can tell by your description that you're having issues because your data isn't in a tabular format. i.e. you have a column for each date. You should have one column containing all the dates and another column for the aircraft count. This will make your table longer instead of wider, but it means the data is more easily summarised and the moving 4 week calculation can be automated without the need to update it.
More on the tabular format you need to strive for here.
You can use Power Query to unpivot the data.
I hope that points you in the right direction.
Mynda
November 28, 2020
Offline
Hi Mynda,
Thank you so much for taking the time to reply, especially as I hadn't even managed to upload the file!
Hopefully I've now attached it but I think you're going to say exactly the same thing, that the structure of my data input is wrong.
regards
Mike
July 16, 2010
Offline
Hi Mike,
Yep, your data is in the wrong format. When you look at the PivotTable on the Pivotmainchart sheet you should have a 'date' field in the Rows area and a single value field in the Values area. You have 29 fields in the values area. This is not how PivotTables are designed to work. If you fix your data everything will come together for you.
The layout is easy fixed with Power Query, as linked to in my reply above. Good luck!
Mynda
Answers Post
November 28, 2020
Offline
Hi Mynda,
well well....I cannot believe (a) how easy that was to do, (b) why I was persevering with my old format for so long, and (c) how much time I've wasted.
I don't always comment or like your videos but rest assured they are all so super helpful.
Thanks for everything!
Mike
1 Guest(s)
