Hello,
I am using Analysis for Office version. 2.2.3.56890 and Microsoft Office 2016 where I have SAP crosstab from the Data Source in one tab and pivot table based on it placed in another tab.
In the query/crosstab I have four dimensions where one of them is Calendar Year/Month in format YYYY-MM.
The problem that I am facing is that although the pivot table is reading data from crosstab it is not considering Calendar Year/Month as a date (printscreen 1). Therefore I cannot filter for dates filter like: current month, previous month - only by label value. My computer date format is set to be YYYY-MM-DD.
In the SAP crosstab where I place the cursor in the cell with Calendar Year/Month data I can see that data is written in format '2016-03 and not 2016-03 (printscreen 2). (The case is for all sap dimensions members.) If the sign " ' " is manually removed from each cell then system is automatically converting data into Mar-2016 and pivot table understands it as date.
I am trying to write a macro which is replacing " ' " with " " but Macro is not reading " ' " sign as existing in the cell since that is done automatically by SAP.
The result that I would like to get automatically is shown on printscreen 3. (here done by manual deleting of all " ' " from each cell of Calendar Year/Month)
Could you please help with understanding how to read Calendar Year/Month in Pivot Table as date.