Reverse Accumulation with Multiple Dimensions

[#1]

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!

[dominicmander]

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. !

[marcus_sommer · Accepted solution]

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

[#4]

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.

[#5]

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!