8 Replies Latest reply: Feb 19, 2013 2:25 AM by Maxim Lopatkin

# cumulative analysis

suppose i have two column date and sales

2006          2300

2007          2700

2008          3100

2009          3500

2010          4500

2011          3900

when i select year 2010, i want show the sales from year 2006 to 2010

• ###### cumulative analysis

So you want to see sales from minimum year to selected year?

Try this as expression (in a text box or as expression in a chart with dimension year):

=sum({<year = {">=\$(=min({1} year))<=\$(=max(year))"} >} sales)

• ###### cumulative analysis

@swuehi

I want the solution for pivot table along with two dimension.

And suppose that when i select a year 2010 , i should see the sales as 16100

=sum({<year = {">=\$(=min({1} year))<=\$(=max(year))"} >} sales) , this expression work in text but when i used in chart in does'nt work..

I want see the Following output

2007    2300

2008    5000

2009    8100

....

...

...

• ###### cumulative analysis

Ok, you haven't mentioned a pivot table and a second dimension so far...

You can get the cumulative in a pivot table using rangesum() function together with chart inter record functions like above. Note that the appropriate chart inter record function will depend on your chart layout. I assume your year is the dimension to the left and second dimension (maybe Customer) is dragged to the top.

Then you can use something like:

=rangesum(above(sum({<year = {">=\$(=min({1} year))<=\$(=max(year))"} >} sales),0,RowNo()))

Hope this helps,

Stefan

• ###### cumulative analysis

Above expression was correct for pivot table..

If possible can i see the folowing output:-

after Apply the formula my pivot table look like this

2007     20

2008     50

2009     90

2010     140

2011     200

2012     270

Suppose now i select year 2009, the output should be like this

2009  90

• ###### Re: cumulative analysis

=aggr(

rangesum(above(sum({<year = {">=\$(=min({1} year))<=\$(=max(year))"} >} sales),0,RowNo()))

, customer, year)

where customer is your second dimension and year your dimension you want to filter.

To make this work, the load order of year must be chronological  (because the aggr() function sorts its dimension in load order, not in the order you set in table properties).

Hope this helps,

Stefan

• ###### Re: cumulative analysis

One problem still remains: if you have periods with no data this expression displays missing. It's no good.

• ###### cumulative analysis

You could also plot a chart with 'year' as the dimension and sales as the expression.

Then in the expressions tab of the properties of the chart, select Full Accumulation and Text as Pop-up

You can then see the cumulative sales over all years at once!

-Nick

• ###### Re: cumulative analysis

You could create new table where each date from your sales corresponds to that date and all dates after that date. F.e.:

Date          DateCum

2006          2006

2006          2007

2006          2008

2007          2007

2007          2008

2008          2008

Then try to use DateCum as a dimension in Bar Chart. I could share script generating such a table if you need it.