Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Previous value in Full Accumulation

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!

1 Solution

Accepted Solutions
pover
Luminary Alumni
Luminary Alumni

Well, there might be a better way to do it, but it works.  Take a look at the attached file and tell me what you think.

Karl

View solution in original post

7 Replies
pover
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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.

pover
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

Karl excellent idea!  It totally works but here is a twist (see attachment), what if there are multiple dimensions in your chart?  How, if possible, do you get a rolling count in that scenario based on when a dimension changes?  So I believe the challenge is how, using the rowno() funtion or some other function, to return row numbers for a dimension and not the entire data set.  For example if I have YEAR and QUARTER as my dimensions in a chart can rowno() function be used to count YEAR rows and when the YEAR changes reset back to 1?

Karl even if this is not possible I really appreciate the help you've been.

pover
Luminary Alumni
Luminary Alumni

Well, there might be a better way to do it, but it works.  Take a look at the attached file and tell me what you think.

Karl

Not applicable
Author

It works and its genious.  This is really cool and I hope others will find is usefull in the future.  Karl thanks again for your help!

Not applicable
Author

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