7 Replies Latest reply: May 9, 2012 5:45 AM by guybenhaim RSS

Previous value in Full Accumulation

David Bonnet

I'm using "Full Accumulation" in one of my line charts to accumulate some percentages.  It works great but I want to be able to STOP the accumulation once it gets to a point.  I think I could be able to do this if I could get check the previous value before it accumulates to see if it has reached my limit.  It seems as though the chart inter record functions are only for pivot tables.

 

Does anybody know if there is a function I can use to return the current accumulated value in my line chart?

Thanks for anything!

  • Previous value in Full Accumulation
    Karl Pover

    I think it would be hard to do with the "Full Accumulation" option.  You can use the above() and below() functions to accumulate values and with an if() also make sure you haven't passed a limit.

     

    The inter record function before() and after() are just for pivot tables, but above() and below() work for any chart type.

     

    Karl

    • Previous value in Full Accumulation
      David Bonnet

      Karl thank you.  That works but it just gives me the value for that specific previous row, not the accumulated total of all the rows that have come through.  I've almost given up on this but if you have any other ideas they would be appreciated.

      • Re: Previous value in Full Accumulation
        Karl Pover

        You're right.  I missed that detail.  The above() function can return a range of values.  For example, above(sum(Sales),0,12) will return 12 previous values.  To sum the values you use the funtion rangesum() and if the range is dynamic you can use the function rowno() to dynamically accumulate all the values in the graph.  So the function would be rangesum(above(sum(Sales),0,rowno())).  I've attached an example.

         

        Karl

  • Previous value in Full Accumulation
    guybenhaim

    Hello, I have a similar issue which I could use help with .....

     

    We have date (yyyy-mm-dd) and product-line dimensions, and need to present for each day the total $ bought over all product-lines, as running accumulation over all past dates ( say from 1/1/2010);

     

    The chart must have date on x axis (so its tricky to play with the order of dimensions).

    The chart should show the each of the latest 60 days (NOT for the entire history); So there is a selection on the date dimension.

     

    The raw data includes date and $ sales for each product line.

    However, on certain dates one (or several) product lines had 0 sales and do not appear in the data. So we have a 'punctured' dimension ....

     

    On those days such product-lines do not get summed at all - yielding a low figure, since the product-line's entire history is omitted from the total on those days.

     

    Any idea of how to avoid punctured dimensions?

    (hopefully, without inflating raw data with rows with 0$ for the missing products)

     

    Thanks in advance.

    Guy