Restrict to 12 values and accumulate in pivot table

[udaya_kumar]

Hi,

I have a pivot table with weekly data like wk51-15,wk52-15,wk53-15,wk1-16,.....

I am restricting it to 12 values by using rank function.

12 weeks = wk51-15 to wk9-16

Now i want to sum the values which are greater than 12 weeks data (wk9-16,wk10-16,wk11-16, and so on) and show that value in 12th week in the pivot table (like accumulated value).

How can we achieve this feature?

[udaya_kumar]

I had to do the samething for bar chart also.

In that bar chart, i could achieve the same thing by using the dimension limit,

Set limit to first 12 values and select "show others" and used Others as the 12th week which is the sum of all the weeks after 12th week. I want the same thing to be done for pivot table.

[Anonymous]

Create a week number at script like:

num(week(DateField)) as WeekNumber

then like this:

=sum({<WeekNumber=,DateField=, WeekNumber={'>=$(=max(WeekNumber)-12)<=$(=max(WeekNumber))'}>} Sales)

check this as well:

Set Analysis for certain Point in Time

[Anonymous]

Not cleared yet...

You want first 12 values only?

or

last 12 week rolling period?

[udaya_kumar]

I want to show 12 weeks from the current week and the for the 12th week, The value (Sum) should be the sum of all the weeks greater than 12th week and show that value in 12th week.

[Anonymous]

Okay, then try what I have already suggested you...

That would help you to get last 12 week from today..

[udaya_kumar]

Ok, but how to get the sum for the 12th week?

Value of 12th week should be equal to sum of 12th week, 13th week,14th week and so on till what ever data is available.

How to achieve this?

[Anonymous]

Could you post some sample data?

[udaya_kumar]

Please check the attached file.