Using 'Before' on Pivot with Sub-Totals

[#1]

Please see the attached example file.

I do not seem to be able to populate the next month after CummValue/Total (e.g April has started at 10 instead of 71) as it seems to be reading a NULL value and then re-starting the cumulative value.

Can anyone help?

[deepakk]

hi Chris,

I would suggest you do it at script level. It would be an easy method.

Thanks & Regards

Deepak

[#3]

Deepak, do you have an example script you could share?

TIA

Chris

[deepakk]

hi Chris,

Please check out the forum..

http://community.qlik.com/forums/t/43895.aspx

I think we have discussed the same issue in this forum.

In the attch application u will find the script part also.

[Miguel_Angel_Baeyens]

Hello Chris,

You have several dimensions and you want to do a sum "avoiding" some of them (in this case, not having into account "Quarter" so it keeps adding month after month).

In your chart try the following expression

RangeSum(Before(TOTAL Sum(Value), 0, ColumnNo(TOTAL)))

Hope that helps.

EDIT: If you want only one expression to get partial sums per quarter but monthly sum accumulation, use something like the following

If(ColumnNo() = 0, Sum(TOTAL Value) , RangeSum(Before(TOTAL Sum(Value), 0, ColumnNo(TOTAL))))

Note that the funcion used is Before() and ColumnNo() but if you pivot some of the dimensions you may need to change to Above() and RowNo() instead.

[#6]

Thanks Miguel,

This is very helpful!

How do I get the Quarter to accumulate e.g Q1 = 61, Apr=10, May=0 & June=10, therefore Q2 needs to show 81.

Chris