Automatic Field selection in a Pivot Table 1

[Recce]

Hi,

We have Pivot table that we display in a Dashboard...
Within this Pivot Table on the left hand side, in other
words, in the row section we have a time period.

Our standard is to only display six time periods at one time
however, when the next time perion arrivee we would like to display this period without having to manualy edit the Pivot table or having to go and manualy select the next time period.

In other words, I would like to know I how we can make this a rolling 6 time period field. By the way, these time periods are not date or time but, merely a number that represent a date i.e.113

Can someone maybe help with any suggestions on how we can do this ?

"We know nothing but, that what we know is not the truth..." - Me

 

[SkipVought]

Hi,

Maybe an example of the data would help us understand your problem.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Recce]

Hi Skip,

Sorry to only reply now...

Yes, let me try and give an example.

Our transaction dates are also represented by a Period indicator in a seperate field in other words, each Period represents different dates i.e. Jan 1st untill Jan 31st could be represented by Period 10. Now, what I need is to show a rolling period of 6 periods at a time in my Pivot Table that will automatically update or roll over to a next period when that period arrives:

10
11
12
13
14
15

When the transactions for period 16 are processed then I need period 10 to fall away and period 16 to be included automatically. I hope this makes for better understanding.

"We know nothing but, that what we know is not the truth..." - Me

 

[SkipVought]

I'd do that with a formula in the Source Data.

"each Period represents different dates i.e. Jan 1st untill Jan 31st could be represented by Period 10."

BUT, without knowing the LOGIC, ie how do periods directly relate to dates, how can you know on what date one period ends and another begins?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Recce]

Hi Skip,

Our time periods always represent the 22nd of the month untill the 21st of the next month...Then the next period starts again at 22nd untill 21st of the following month.

"We know nothing but, that what we know is not the truth..." - Me

 

[SkipVought]

How do you associate a SPECIFIC period VALUE with a date? Need you to provide ALL the details, not a drip at a time!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Recce]

Hi Skip,

The periods and the dates are prepopulated in the databae untill 2020 in a dates table. Something like this:

Period Date
10 20080821
... ...
10 20080920
11 20081021
... ...
11 20081120
12 20081221
... ...
12 20081220

Now, what I need to know, is if it would be possible for the next period to be selected automatically when ever there are transactions associated with this next period. In other words, I need this field to be a sort of a rollong selction of 6 months all the time.

I don't want to go into the Pivot Table all the time in order to go and select the next month for the user manually. The Pivit Table Period field must do this automatically whenever there is data for this next period.

Hope this is a bit more clear.
14
15


"We know nothing but, that what we know is not the truth..." - Me

 

[SkipVought]

"The periods and the dates are prepopulated in the databae untill 2020 in a dates table."

I would join the Dates Table with your Source Data, and return ONLY the rows that you want to pivot.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Recce]

Hi Skip,

Yes, I must be honest, I have manged to do something like this but, now business says that they want to see all dates and data so, I was just hoping that maybe there is another way but, what you said at the top does work

Thanks for this...

"We know nothing but, that what we know is not the truth..." - Me

 

[SkipVought]

Hmmm? They want to see ALL the data AND they only want to see a rolling 6 months? Maybe its OR.

Again, use a formula in the SOURCE DATA to group the 6 month data. Then, put this field in the PAGE area. They can eithe select ALL, or the GROUP representing the rolling 6 months.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Recce]

Hi Skip,

That's an even better idea !
Thank, I'll play around with that as well....

"We know nothing but, that what we know is not the truth..." - Me