topic Accumulation in a pivot table in QlikView

Accumulation in a pivot table

Thu, 23 Feb 2012 13:00:54 GMT

Hi everyone,

I am facing some problems to display an accumulated amount in a pivot table. Currently, I am working on members registration (with a registration date) and i want a pivot table looking like this :

Year	Month	# new members	# members from the start
2011	Dec	10 000	20 000
2012	Jan	5 000	25 000
	Feb	6 000	31 000

Assuming that the first members registred on November 2011, the previous table is showing data filtered by an active selection (from Dec 2011 to Feb 2012).

My members table looks like this (without a few dimensions such as language_id, channel_id..) :

member_id
member_registration_date
member_count // always equal to "1"

Getting the first expression # new members is quite simple : SUM(customer_count)

Now, I want, in the second expression, the # of members from the start (ie. Nov 2011) to the end of the month. Indeed, the first line of my table (Dec 2011) should be : # new members in Nov 2011 (10.000) + # new members in Dec 2011 (10.000) = 20.000

However I tried many set analysis and expressions, I couldn't find a way to do this...

Thanks in advance for your help,

Damien

Accumulation in a pivot table

jagan — Thu, 23 Feb 2012 13:08:46 GMT

Hi,

Try the following expression

=RangeSum(Above(TOTAL Sum(NewMembers), 0, Rowno(TOTAL)))

Regards,

Jagan

Accumulation in a pivot table

Thu, 23 Feb 2012 13:42:04 GMT

Hi Jagan,

Thanks for your quick answer.

It's almost what I want but, in your expression, my table looks like this :

Year	Month	# new members	# members from the start
2011	Dec	10 000	10 000 (instead of 20 000)
2012	Jan	5 000	15 000 (instead of 25 000)
	Feb	6 000	21 000 (instead of 31 000)

My # new members in Nov 2011 (not part of the current selection) is not use for the calculation.

Damien

Accumulation in a pivot table

jagan — Thu, 23 Feb 2012 13:51:15 GMT

Hi,

The expression is correct, The first row has 10000, the second row has 5000. So the sum of first 2 rows is 15000(10000 + 15000). Please check your data.

Regards,

Jagan.

Accumulation in a pivot table

Thu, 23 Feb 2012 13:59:04 GMT

Hi,

The table shows the result for a selection from Dec 2011 -> Feb 2012.

However, my data starts from Nov 2011 (10.000 new members) and I would like to have it in my table.

That's why I want to have in my first row the sum of Nov 2011 (10.000) and Dec 2011 (10.000). In the second row, the sum of Nov 2011 (10.000), Dec 2011 (10.000), Jan 2012 (5.000) and so on.

Regards,

Damien

Accumulation in a pivot table

jagan — Thu, 23 Feb 2012 14:03:55 GMT

Hi,

Please attach the sample file.

Regards,

Jagan.

Accumulation in a pivot table

jagan — Thu, 23 Feb 2012 14:35:03 GMT

Hi,

In pivot table it is not possible to get previous month values, it is only possible to get previous row values visible in the chart.

Regards,

Jagan.

Accumulation in a pivot table

Thu, 23 Feb 2012 15:28:31 GMT

Hi,

Ok... thanks for your research !

I'll try another way to do this...

Best regards,

Damien

Re: Accumulation in a pivot table

CELAMBARASAN — Fri, 24 Feb 2012 04:44:09 GMT

Hi,

Try with this approach

Set variable with value =Sum({1<member_registration_date={"<$(=Min(member_registration_date))"}>} member_count)

Use this expression in pivot table

=Sum(member_count)+Alt(Above(TOTAL [Accumulate]),vAccumulation)

Here vAccumulation is a variable.

[Accumulate] is expression label of above expression.

Or you can try with this expression instead of using variable

=Sum(member_count)+Alt(Above(TOTAL [Accumulate]),$(=Sum({1<member_registration_date={"<$(=Min(member_registration_date))"}>} member_count)))

Hope it helps

Celambarasan

Re: Accumulation in a pivot table

perumal_41 — Fri, 24 Feb 2012 06:23:25 GMT

I attached One Application. Please Check is correct or not .

Regards,

Perumal A

Re: Accumulation in a pivot table

Fri, 24 Feb 2012 09:52:25 GMT

Hi all,

Thanks everyone for your time and your answers.

Celambarasan, it helps a lot !

It works perfectly fine on a classic pivot table. However, I dragged the dimensions to set them vertically, and the expressions to set them horizontally, and the above() function doesn't seem to work very well (null value)

Any ideas ?

Regards,

Damien

Accumulation in a pivot table

jagan — Fri, 24 Feb 2012 09:59:02 GMT

Hi,

Instead of Above() use Before().

Regards,

jagan.

Re: Accumulation in a pivot table

CELAMBARASAN — Fri, 24 Feb 2012 10:03:48 GMT

Hi,

For horizontal calculation you have to use Before instead of Above.

Celambarasan

Re: Accumulation in a pivot table

Fri, 24 Feb 2012 10:05:48 GMT

Guys ! You're all great !

To sum up everything :

I used a variable :

vMembersAccumulation = Sum({1<member_registration_date={"<$(=Min(member_registration_date))"}>} member_count)

The expression is (with vertical dimensions and horizontal expressions) :

[# members] = SUM(member_count) + ALT(Before(TOTAL [# members]), vMembersAccumulation)

Thanks a lot,

Regards,

Damien

Re: Accumulation in a pivot table

Tue, 05 Mar 2013 15:37:33 GMT

Thank you so much Jagan Mohan

jagan mohan wrote:
Hi,
Try the following expression
=RangeSum(Above(TOTAL Sum(NewMembers), 0, Rowno(TOTAL)))
Regards,
Jagan

That does the trick of calculating running totals for any Journal regardless of the dimensions - just need a straight table and this calculated expression.