topic Re: Reverse Accumulation with Multiple Dimensions in QlikView

Reverse Accumulation with Multiple Dimensions

Fri, 22 Jan 2016 16:47:18 GMT

Hi Everyone,

I'm having an issue trying to get a line chart with reverse accumulation to display multiple dimensions.

The requirement is to show the decaying subscriptions across different product types from a cohort of customers. I've attached a .JPG of what I'm trying to do (fudged in Paint).

My chart works as expected when I have the single DAY dimension but when I try to bring in the PRODUCT dimension it ignores my RangeSum(Below(... expression and just plots the DISCONNECTIONS measure.

Interestingly, it displays the straight table as expected (use the quick change in the attached .QVW). It looks like this is because the sort priority of the straight table has product first. I can't do this in the line chart as it messes up what is actually being plotted.

I have looked elsewhere in the community but haven't managed to find a solution yet.

Has anyone managed to do something similar before?

Any help would be greatly appreciated!

Re: Reverse Accumulation with Multiple Dimensions

dominicmander — Fri, 22 Jan 2016 17:55:54 GMT

I can get there in a straight table as follows ...

sum(Total <PRODUCT> [DISCONNECTIONS])

rangesum(above(sum(DISCONNECTIONS),0,count(TOTAL <PRODUCT> DAY)))

I have attached my qvw with the separate columns so that you can see my thinking.

Unfortunately this doesn't work when sorted on DAY before PRODUCT (which is what will happen in a line chart). It's getting a bit late but I might take a second look later ... or maybe this gets you far enough to finish it off. !

Re: Reverse Accumulation with Multiple Dimensions

marcus_sommer — Fri, 22 Jan 2016 17:57:09 GMT

If you have only a few products like in your screenshot you could remove them as dimension and used instead two or more expressions with set analysis as product-filter, like: sum({< Product = {1}>} Value).

- Marcus

Re: Reverse Accumulation with Multiple Dimensions

Sat, 23 Jan 2016 15:25:53 GMT

Thanks guys.

Good idea regarding set analysis. There are a ton of products, but I might be able to get around that by having the user select a few at a time to compare and hold them in variables to use in the set modifier. The user would never want to compare more than a couple at a time anyway so it shouldn't be a problem.

I'll try it out first thing in Monday morning and let you know how I get on.

Re: Reverse Accumulation with Multiple Dimensions

Mon, 25 Jan 2016 09:08:51 GMT

Thanks guys. The set analysis method worked a treat!

I created a couple of variables and then set them using input boxes that listed all the values from PRODUCTS

Formula: "=Concat({1} distinct PRODUCT,',')"

Then it was just one expression per product in the chart

Formula "=RangeSum(Below([Product A]), sum({<PRODUCT = {$(vProductA)}>}DISCONNECTIONS))"

Because there is still only one dimension in the chart, QlikView can still work out the RangeSum based on the reverse accumulation by DAYs.

Thanks again!