search

OfficeDev / office-js-docs-pr

Microsoft Office Add-ins Documentation
https://learn.microsoft.com/office/dev/add-ins
Creative Commons Attribution 4.0 International
403 stars 248 forks source link

Group date by year and month in pivot table #3044

Closed ruslan-oncoshot closed 2 years ago

ruslan-oncoshot commented 2 years ago

Using Excel JavaScript API I can add pivot table and can add columns for each date value

let dateHierarchy = pivotTable.addColumnHierarchy(pivotTable.getHierarchy("Date")); but it shows as individual dates.

How can I group these dates by year, quarter and month?

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

peronald commented 2 years ago

Thank you for this question. To better assist you, we suggest you go to Stack Overflow and see if your question has already been asked or ask a new question. This will allow both the community to help, and allow us to ensure the community can see our answer. When added, please let us know and we will follow up there. Thanks, Microsoft 365 Developer Platform team.

ruslan-oncoshot commented 2 years ago

I asked it on stack overflow first https://stackoverflow.com/questions/69809107/group-date-by-year-and-month-in-pivot-table

There's no tag for that and no community

Where can i seek help?

ruslan-oncoshot commented 2 years ago

@peronald please

AlexJerabek commented 2 years ago

Reopening. @alison-mk, could we investigate if this functionality is in the Office JS API and, if so, add a sample?

alison-mk commented 2 years ago

Hi @ruslan-oncoshot,

The DateFilterCondition enum allows for filtering by month and quarter, and an equals field that could be used for year. The following code sample excerpt shows how to use the PivotDateFilter to include only dates in July.

    const dateFilter = {
      condition: Excel.DateFilterCondition.allDatesInPeriodJuly,
    };
    filterField.applyFilter({ dateFilter: dateFilter });

Does this answer your question? If not, can you describe the type of grouping that you want to do?

Thanks, Alison

ruslan-oncoshot commented 2 years ago

Hi @alison-mk thanks for suggestion.

What I mean is PivotTable Analyze -> Group Field option which is as I understand not available in Web version of Excel. I was hoping I could use Excel Script to get grouping by years and months image

alison-mk commented 2 years ago

Hi @ruslan-oncoshot, apologies for the delay. I'm investigating your question internally will provide an update to this issue when I have more information.

Thanks for your patience, Alison

alison-mk commented 2 years ago

Hi @ruslan-oncoshot, unfortunately the PivotTable feature you're looking for isn't currently available. To request this feature, we recommend submitting via Microsoft 365 Developer Platform Ideas. We use Microsoft 365 Developer Platform Ideas to prioritize community requests for additions to the product. If the community upvotes your request, it's more likely to be added to the add-ins platform.

Since this isn't a bug, I will close this issue. Let me know if you have any questions!

Thanks, Alison