6 Replies Latest reply: Mar 5, 2013 4:25 AM by markvandermeij

# Cumulative calculation with set analyses, ignoring selection (example included)

Hi,

I'm trying to generate cumulative results over the total period of time, even if there are some selections made.

For example. if the user selects the last 3 weeks of a year, I still want to see the total value over all time.

The user shuold be able to make other selections though, like on article level.

I

weekvaluecumm value
2012.w521010
2013.w11020
2013.w21030

in the above selection, if the user selects week 2013.w1 & week 2013.w2, the cumm value should still be 30.

An example file with data and selections is included.

Who can help? Thanks a lot!

• ###### Re: Cumulative calculation with set analyses, ignoring selection (example included)

Afaik you can only do that if you calculate the running total in the script. See attached example

• ###### Re: Cumulative calculation with set analyses, ignoring selection (example included)

Hi,

I've been trying to calculate that way in the large script. I'm running into the issue that there are many side dimensions though. The example in my script is the "Article A and Article B". Cause that really should affect the grand cummulative total.

Am I going to have to take all those into account too in the script? Or is there another sollution?

• ###### Re: Cumulative calculation with set analyses, ignoring selection (example included)

Hey there,

If you want to do this as an expression in a chart try this

Sum({\$<Year =,YearWeek=,WeekNumber=>} Value)

That calculates the totals for those fields and also allows you to make selections on other dimensions

-ART

• ###### Re: Cumulative calculation with set analyses, ignoring selection (example included)

Pherhaps you can use

Sum(ALL Value)

It returns the Sum of all dimensions too.

regards,

• ###### Re: Cumulative calculation with set analyses, ignoring selection (example included)

Yes, but this does not provide the cumulative calculation I'm looking for. (See top table an example file)

• ###### Re: Cumulative calculation with set analyses, ignoring selection (example included)

I think that you might want to try:

rangesum(first({\$<Year =,YearWeek=,WeekNumber=>} Value,1,RowNo())).

You might need to fiddle with it to make sure that RowNo() is returning the right result, though.

Just tried it on your sample set, that apparently only works on pivot tables.