Percentages and full accumulation in chart

[#1]

Hi

I use "full accumulation" setting in a chart to add up 2 different montly values in a YTD scenario (Year is fixed with a SET expression).

I tried to use Column(2) / Column(1) for the percentage value but this is not using the accumulated values but the monthly ones.

How to get the percentage of the 2 accumulated values?

Thanks

Juerg

[Oleg_Troyansky]

Yes, as you are discovering, "Full Accumulation" doesn't work well with calculations using the accumulated columns...

I'd suggest to try "manual" accumulation - using functions above() or before().

[prieper]

Think that you need to build the accumulation manually with a formula like

RangeSum(SUM(YourValues), Above(ThisExpressionName))

build one for each expression, then you may work with Column(2) / Column(1).

HTH
Peter

[#4]

Hi Peter

Thanks for your reply.

I have changed my chart now and use

rangesum(sum({<Jahr={'$(=year(max(MonatJahr)))'}>}AustrittMA),above(sum({<Jahr={'$(=year(max(MonatJahr)))'}>}AustrittMA),0,MonatNr))
/rangesum(sum({<Jahr={'$(=year(max(MonatJahr)))'}>}MA),above(sum({<Jahr={'$(=year(max(MonatJahr)))'}>}MA),0,MonatNr)) ) * 12

which shows the expected values now. I however get now the previous year shown in the chart, it seems to ignore the Presentation settings "Suppress Zero-Values" and "Suppress Missing"?

[saxjonas]

Are the values zero or null?

Try ticking the "Suppress When Value Is Null" box under Properties->Dimensions.

[Oleg_Troyansky]

Yes, I'm also getting this "side effect" every time I use above() - looks like this function is causing QlikView to show all possible values of the Date dimension, even if there is no activity associated with it.

As a work-around, I'm always conditioning my accumulated expression by the fact that the original expression is not empty.

For example:

Expression 1: Monthly Sales = sum(Sales)

Expression 2: moving average of sales: IF (Column(1) <> 0, ..., null())

This way, the extra months when there is no sales, disappear...

[#7]

Sorry for posting a subsequent question on your thread.

I am also trying to calculate monthly accumulated percentage by year on a chart, and applying your rangesum(A)/rangesum(B) works in a pivot table but I cant get it to accumulate in a chart.

Have I missed something?

Attached qvw with clearly shows accumulation works in a pivot but cannot replicate results in the chart.