topic Using 'Before' on Pivot with Sub-Totals in QlikView

Using 'Before' on Pivot with Sub-Totals

Mon, 11 Apr 2011 18:07:42 GMT

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?

Using 'Before' on Pivot with Sub-Totals

deepakk — Tue, 12 Apr 2011 04:58:20 GMT

hi Chris,

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

Thanks & Regards

Deepak

Using 'Before' on Pivot with Sub-Totals

Tue, 12 Apr 2011 07:38:20 GMT

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

TIA

Chris

Using 'Before' on Pivot with Sub-Totals

deepakk — Tue, 12 Apr 2011 08:17:55 GMT

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.

Using 'Before' on Pivot with Sub-Totals

Miguel_Angel_Baeyens — Tue, 12 Apr 2011 09:06:56 GMT

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.

Using 'Before' on Pivot with Sub-Totals

Tue, 12 Apr 2011 09:25:07 GMT

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