I want It looks like the one on the Sheet 4. My table is on the Sheet 1.
How can I do it?
I want It looks like the one on the Sheet 4. My table is on the Sheet 1.
How can I do it?
See attached.
You needed to Group the PT Date by Month.
Since the underlying data doesn't contain some months you won't see them shown in the PT. You would need to add dummy rows to the data
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
1) How can group PT Table by months?
2) How can I add dummy rows to the data?
3) Your table have missing monhts like April and September. When I check Show Items With No Data It shows unnecessary items like "<22/01/20"?
How can I fix this?
1. The Workbook I sent is grouped by months. Put the Date field only in the Rows area of the PT Field List pane and put the Month field in the Values Section and set the 'Value' to COUNT
2. Add a Date only in column A of the data and change the PT Source Data Range
3. With the PT Menu (or right click in the Date field of the PT), choose GROUP and then pick Months.
1) Put The Date Date field into the Rows section. Put Months Into The Values Section. Set COUNT. But It doesn't look right.
2) I inserted a new row on the Date column.
3) I grouped the rows.
What do I do wrong?
You added a whole row to the data. I said to just insert a date in column A
The file you attached was linked to an external database so obviously I couldn't access that. The PT on the attached is linked to the data in this file.
I also noticed a value called 'formula' in the PT. I've no idea what that was meant to ne so have removed it.
I could do this mostly.
1) Although I unchecked "blank" at Pivot Table They still appear both Pivot Table and Pivot Chart.
Pivot Table (The Month April, W1, W2, W3, W4, blank)
There is no week named blank. ?
2)Pivot Chart X Axis looks like stuffed. Characters overlap each other. How can I spread them?
You have specified the whole of columns A:E as the PT Source data, hence you are getting blanks. Limit the source range to the table.
Widen the chart, or change the font size of the horizontal axis or better still move the embedded chart to its own Chart Sheet
Last edited by Richard Buttrey; 09-30-2020 at 06:10 PM.
Yes, I did that on purpose.
I'll continuously enter new values on that table. And I want every new data will show up in the pivot chart.
How can I do that?
If you continue to use A:E as the PT source then you will get blanks in the PT.
Are you now saying you will be limiting the source range?
Then I assume there is not another way for this. I'll give up showing weeks in this case.
I choose only the month May in Timeline. However now It show nothing on the pivot chart.
Although May has 5 entries.
???
To which Pivot chart are you referring? The one on Sheet1 or Sheet4.
The May Count is on the Sheet1 chart, the only reason you can't see the Total Series is because the chart is a line chart. Change it to a column chart.
Thank you very much for the answers Richard Buttrey.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks