Accumulation in Pivot Table

[#1]

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!

[#2]

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

[Nicole-Smith]

Can you post an example?

[#4]

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))
)
)

[#5]

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....

[Nicole-Smith]

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.

[Nicole-Smith]

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

[#8]

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+

[#9]

Im sorry im new to this...

[#10]

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

=(
((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))
)
)

[Nicole-Smith]

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))

)

)