1

I want to show data in a pivot table, grouped by month and year, showing items for all months, including months without data. I followed this tutorial. However, this doesn't work as I want when I additionally group by year.

Example:

2015/11/3
2016/1/7
2016/3/19

appears in the pivot table like this, if "Show items with no data" is not checked:

2015
    Nov    1
2016
    Jan    1
    Mar    1

If it is checked, it will show:

2015
    Jan    0
    Feb    0
    Mar    0
    Apr    0
    May    0
    Jun    0
    Jul    0
    Aug    0
    Sep    0
    Okt    0
    Nov    1
    Dec    0
2016
    Jan    1
    Feb    0
    Mar    1
    Apr    0
    May    0
    Jun    0
    Jul    0
    Aug    0
    Sep    0
    Okt    0
    Nov    0
    Dec    0

What I actually want is this:

2015
    Nov    1
    Dec    0
2016
    Jan    1
    Feb    0
    Mar    1

How can I do this?

1 Answer 1

0

This is more of a workaround than a direct fix, but would it be out of the question to put a value for 0 sales in December and February into your data? I don't know how your data is formatted, but even if it's daily, you could put in a value for the days 11/1 and 2/1 with 0 sales, which would count as 0 sales for the month.

This would allow the pivot table to pick up those months and display them, with "Show items with no data" unchecked.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.