topic Previous value in Full Accumulation in QlikView

Previous value in Full Accumulation

Mon, 19 Mar 2012 23:10:47 GMT

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

pover — Tue, 20 Mar 2012 05:41:19 GMT

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

Tue, 20 Mar 2012 23:46:06 GMT

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

pover — Wed, 21 Mar 2012 14:58:52 GMT

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

Re: Previous value in Full Accumulation

Wed, 21 Mar 2012 15:47:26 GMT

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.

Re: Previous value in Full Accumulation

pover — Thu, 22 Mar 2012 02:45:32 GMT

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

Re: Previous value in Full Accumulation

Thu, 22 Mar 2012 16:06:00 GMT

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!

Previous value in Full Accumulation

Wed, 09 May 2012 09:45:57 GMT

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