running total with partially collapsed dimension

[jedgson]

Hi, I have a pivot table with 3 dimensions - Year, Month, Day - and an expression with a running total, ie day2 = day1 + day2, day3 = day1 + day2 + day3, etc, etc. This works fine however when I collapse a value in the second dimension (month) it will not include the data from the collapsed value in the total. (eg I will show all days in January and March but only the month for February)

I tried to upload an example however received some error messages in chrome and IE9 wont allow me to type in the description box of a new post 😞 so heres a screen shot instead ...

As you can see from the screen shot, the running total does not include the data from the Feb which is collapsed. Is there a way to continue the total across this collapsed value. The total on the 1st March would then be 31 (total on 31st Jan) + 28 (February's total) + 1 (March 1st value) = 60.

[jedgson]

Any suggestions?

[maneshkhottcpl]

Hi,

Please refer the attached files and if it is not useful please post me a sample data in excel. I will help u.

[maneshkhottcpl]

Hi,

Also refer this excel file.

[jedgson]

Thanks Manesh.

I didn't think of using Peek in the script! The only problem is that when you collapse month it dissapeared from the chart. I got round this by using =max(NewTotal) in the expression instead on =only(NewTotal)

I will try this on my actual data and let you know how I get on.