Current period in Pivot table

Replies to This Discussion

Permalink Reply by Terence Hinrichsen on January 3, 2012 at 3:10pm

How big is your raw data? Can you use an excel function? The problem with a sql function to determine the current month is that it can only be changed by re-running the report and selecting the other month. The only way you would be able to do this in Excel is by dumping all the months and using an excel function like the sumifs or even just an if function to only show the values for the current month selected. If your raw data is too big i cannot help, otherwise I can set up an example for you.

• ▶ Reply

Permalink Reply by Gareth Holley on January 4, 2012 at 8:30am

Hi Terence,

Thanks for the assistance. I think I know where you are going with your thinking except my only issue that that this is all being handled in the Pivot table. Are you saying that maybe I should not be using a pivot table?

The reason I went for the pivot table option was to get the accounts running across the top of the report and then only showing the specific accounts that I require. I could not achieve this in a conventional Alchemex report (maybe I was  missing something here)?

Regards

Gareth

• ▶ Reply

Permalink Reply by Arthur de Breyn on January 19, 2012 at 7:01pm

Hi Gareth,

Maybe the attached can help.

Selecting the current month from the combobox updates the pivot table.

It's a VBA solution.

If you want to avoid VBA, then you can do the following:

Add the calculated fields as expressions in your data container (see the formula in the last two columns on Sheet1)

Select the month you want to view.

Refresh the pivot table manually.

Regards,

Arthur

Attachments:

• pivot-with extra dropdown.xlsm, 47 KB

• ▶ Reply