topic Re: Re: Accumulation in Pivot Table in QlikView

Accumulation in Pivot Table

Tue, 28 Jan 2014 19:28:44 GMT

Hi everyone,

I’m trying to accumulate some values inside a Pivot Table (so I can use horizontal dimensions). The challenge is that I need to accumulate month by month only certain values whilst using non-accumulated values for the second part of the expression.

I’m attaching a sample file in which the example will become clearer.

Thanks in advanced!

Re: Accumulation in Pivot Table

Anonymous — Tue, 28 Jan 2014 19:40:33 GMT

How to sum across rows in a pivot table (running total)

Attached is how to do it with your example .

Re: Accumulation in Pivot Table

Tue, 28 Jan 2014 19:50:51 GMT

Hello Michael, ive already try using the function "Before" but it wont work because i need to sum the accumulation of values "Type A" and then sum the corresponding "Value B"

Re: Re: Accumulation in Pivot Table

Nicole-Smith — Tue, 28 Jan 2014 20:13:09 GMT

From what I'm seeing, it looks like you can't do it in one expression. In my attached file, when I calculate the accumulation of A and just the sum of B in two different expressions, then add those two together, it works fine. The problem comes in when I try to put the two expressions into one. Maybe someone else can chime in if they have any ideas because I'm stumped .

Re: Accumulation in Pivot Table

Tue, 28 Jan 2014 20:14:55 GMT

Thanks Nicole but that is exactly the problem i need them to be in just one expression

Re: Re: Accumulation in Pivot Table

Anonymous — Tue, 28 Jan 2014 20:21:54 GMT

Try the attached. I mapped the months to periods. That way you can multiply the month by Value A and add Value B.

So Jan = 1 so 1*Value A +Value B

Feb = 2 so 2*Value A +Value B

and so on.

Re: Accumulation in Pivot Table

b_garside — Tue, 28 Jan 2014 20:22:38 GMT

Try a formula like this;

This works Left to right. Pivot table

RangeSum(Before(Sum(YourField),0, ColumnNo()))

Top to bottom

RangeSum(Above(Sum(YourField),0, RowNo()))

Re: Re: Accumulation in Pivot Table

Nicole-Smith — Tue, 28 Jan 2014 20:28:02 GMT

Got it!

=RangeSum(Before(sum({$<Type={'Value A'}>} Value),1,12), sum({$<Type={'Value A'}>} Value))+sum({$<Type={'Value B'}>} Value)

Also see the attached.

EDIT: I forgot to mention that the 1,12 in the before() is accumulating 12 steps back (1 year), as I wasn't sure on your exact requirement. If you want to accumulate over everything, replace the 12 with ColumnNo(TOTAL).

Re: Re: Accumulation in Pivot Table

Clever_Anjos — Tue, 28 Jan 2014 20:34:13 GMT

I´m not sure if I understand you requirement, but try this:

rangesum(
  sum({$<Type={'Value B'}>} Value),
  Before(
  sum({$<Type={'Value A'}>} Value),
  0,
  ColumnNo(TOTAL)
  )
)

Re: Re: Accumulation in Pivot Table

Anonymous — Tue, 28 Jan 2014 20:34:32 GMT

Well your solution is more elegant than mine as usual .

Re: Accumulation in Pivot Table

Wed, 29 Jan 2014 16:37:01 GMT

Thanks Nicole i thought that would work but it wont cause in the real problem i have to accumulate 3 different values and then sustract other non accumulated values so when i use "Before" in two different parts of the expression one of them is not calculated right

Re: Accumulation in Pivot Table

Nicole-Smith — Thu, 30 Jan 2014 16:51:05 GMT

Can you post an example?

Re: Accumulation in Pivot Table

Wed, 05 Feb 2014 18:55:53 GMT

Hi Nicole here is the expression i make using you example but it is not working, thanks in advance for all your help

=(
((sum({$<InvoicesStatus={'A','PS'}, Year={'*'}, Month={'*'}, InvoiceDate={'<=$(=max(InvoiceDate))'}> }RestinAmount) - sum({$<InvoicesStatus={'A','PS'}, lAnticipo={'1'}, Year={'*'}, Month={'*'}, InvoiceDate={'<=$(=max(InvoiceDate))'}> }RestinAmount)) * 360)
/
(
(((sum(Amount)-sum(NoteAmount)-sum(CancelAmount)) / Count({$<Laborable={'1'}>}DISTINCT InvoiceDate))*sum({$<Year={$(=max(Year))}>} Inhabiles)) - sum({$<InvoicesStatus={'PS','A'}, lAnticipo={'1'}, Year={'*'}, Month={'*'}, InvoiceDate={'<=$(=max(InvoiceDate))'}> }RestinAmountSinIva) - (((sum({$<TipoPago={'Contado'}, InvoicesStatus={'P'}>} Amount)-sum( NoteAmount)-sum( CancelAmount)) / Count({$<Laborable={'1'}>}DISTINCT InvoiceDate))*sum({$<Year={$(=max(Year))}>} Inhabiles))
)
)

Re: Accumulation in Pivot Table

Wed, 05 Feb 2014 19:01:05 GMT

Hey guys,

I am given a task to calculate the frequency of calls across a territory. If the rep called a physician regarding the sale of the product 5 times, then frequency is 5 and HCP count is 1....I generated frequencies from 1 to 124 in my pivot table using a calculated dimension which is working fine. But my concern is :

My manager wants frequencies till 19 in order from 1..2..3..4...5..6.....19...

And from the frequency 21-124 as 20+.

I would be grateful if someone helps me with this.....Eager for the reply....

Re: Accumulation in Pivot Table

Nicole-Smith — Wed, 05 Feb 2014 19:03:06 GMT

Abhi Hariharan - Please stop posting your questions on a bunch of other people's posts. If you create a discussion thread of your own, you're more likely to get an answer.

Re: Accumulation in Pivot Table

Nicole-Smith — Wed, 05 Feb 2014 19:06:33 GMT

Which parts of your expression are supposed to be accumulated and what part is supposed to not be accumulated?

Re: Accumulation in Pivot Table

Wed, 05 Feb 2014 19:10:14 GMT

Territory name Frequency 1 2 3 4 5 6 7 8 9 11 15 17 19 21 29 31 39 51 124

British Columbia 234 345 and so on...........

On the frequency above... from 21 to 124 i have to show it as 20+

Re: Accumulation in Pivot Table

Wed, 05 Feb 2014 19:10:28 GMT

Im sorry im new to this...

Re: Accumulation in Pivot Table

Wed, 05 Feb 2014 19:13:13 GMT

Thanks Nicole here i underline the parts that have to be accumulated

Re: Re: Accumulation in Pivot Table

Nicole-Smith — Wed, 05 Feb 2014 19:26:59 GMT

Try something like this:

=(

(

rangesum(before((sum({$<InvoicesStatus={'A','PS'}, Year={'*'}, Month={'*'}, InvoiceDate={'<=$(=max(InvoiceDate))'}> }RestinAmount) - sum({$<InvoicesStatus={'A','PS'}, lAnticipo={'1'}, Year={'*'}, Month={'*'}, InvoiceDate={'<=$(=max(InvoiceDate))'}> }RestinAmount)),0,12))

* 360)

/

(

(((sum(Amount)-sum(NoteAmount)-sum(CancelAmount)) / Count({$<Laborable={'1'}>}DISTINCT InvoiceDate))*sum({$<Year={$(=max(Year))}>} Inhabiles)) -

rangesum(before(sum({$<InvoicesStatus={'PS','A'}, lAnticipo={'1'}, Year={'*'}, Month={'*'}, InvoiceDate={'<=$(=max(InvoiceDate))'}> }RestinAmountSinIva) - (((sum({$<TipoPago={'Contado'}, InvoicesStatus={'P'}>} Amount),0,12))

-sum( NoteAmount)-sum( CancelAmount)) / Count({$<Laborable={'1'}>}DISTINCT InvoiceDate))*sum({$<Year={$(=max(Year))}>} Inhabiles))

)

)