Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Problems with full accumulation in a straight table

I am doing a full accumulation in a straight table which contains 8 dimension columns (something like: category, subcategory, group, subgroup, article, etc) and 10 expressions (all with set conditions and some of them also using "total" identifier): the expression that I am trying to accumulate is of the form: sum({$<YEAR = {'$(vYear)'}>} Sales). I tried to make the accumulation on other expressions from the straight table and still doesn't work: it returns the result like I never checked "full accumulation" condition. Has full accumulation something to do with the number of columns/expressions or with the presence of set expressions? With the same data, but deleting the set condition, the accumulation worked. Does anyone have a hint on what the problem might be? I searched the forum and the reference manual, but I haven't found anything on this topic.Thank you.

7 Replies
Not applicable

Problems with full accumulation in a straight table

hi ,

probably the single quotes is causing the problem

use sum({$<YEAR = {$(vYear)}>} Sales)

thanks

Not applicable

Problems with full accumulation in a straight table

I just tried without single quotes, but it's the same situation. The expression is working very well, the problem is that it ignores the fact that I have checked "full accumulation" condition.

Not applicable

Problems with full accumulation in a straight table

I have also attached an example to further explain my problem.

MVP
MVP

Problems with full accumulation in a straight table

Full accumulation APPEARS to only work when there is only one dimension. That's strange, since it then behaves DIFFERENTLY than the above() function, which "works" when there is more than one dimension, but only for the LAST dimension when all other dimension values are the same. I believe the help text calls this the "column segment". I'm surprised that full accumulation doesn't work within the column segment.

Mind you, even if it did, which I can mimic with rangesum(above(Accum),Sales), it wouldn't solve your problem. You want accumulation period. You don't care what the column values are.

It doesn't have anything to do with your set analysis. You can remove that completely, simply select the year, and you'll get the same results - no accumulation.

A horrible "solution" is to concatenate your dimension fields. Then it adds up correctly. Looks terrible, though.

There must be an easy way. This seems so basic.

MVP & Luminary
MVP & Luminary

Problems with full accumulation in a straight table

I recommend using function above() with keyword TOTAL:

rangesum(above(total Column(3)), sum( {$<Year = {$(vYear)}>} Sales))

(In this example Expression(3) is the same expression, it's a recursive function calling above() of the same column.

The keyword TOTAL is forcing the "Total" accumulation throughout all other Dimensions.

Not applicable

Problems with full accumulation in a straight table

I am pretty surprised to find out that full accumulation only works for straight tables with one expression!

I have tried the solution proposed by Oleg, it works fine, but there is one drawback attached to it: it doesn't allow sorting - I tried to sort my table in descending order by sales.

Not applicable

Problems with full accumulation in a straight table

I found this topic very helpful for my situation, but I faced the same drawback Banciu found: table isn't sortable by sales, sadly. And I need this table sorted out, otherwise the progressive sum is useless for my aim.

Has anyone found a solution, please?