14 Replies Latest reply: Aug 25, 2015 4:55 AM by Digvijay Singh

# Pivot table Calculation Help

Hi Folks ,

I have a pivot table :

Dimmension: STDDATE , Location

Expression : Sum(Values)

Requirement : I need to divide "CHESTERFIELD" values with other location row wise.

e.g for STDDATE- 2015-08-17 . the entire row need to be divide by 47 , similarly for 2015-08-18 row should be divided by 46 and so on...

Kindly suggest how this can be achieved .

Thanks,

AS

• ###### Re: Pivot table Calculation Help

I read Before and After functions which works only for Pivot table few days back, will give a try now.

• ###### Re: Pivot table Calculation Help

I have PE so cannot see your file, please see attached if it can help.

Something like below can work -

If(ColumnNo()=1,Sum(Value)/After(Sum(Value),2),

If(ColumnNo()=2,Sum(Value)/After(Sum(Value),1),

If(ColumnNo()=3,Sum(Value)/Sum(Value),

If(ColumnNo()=4,Sum(Value)/Before(Sum(Value),1)))))

• ###### Re: Pivot table Calculation Help

Digvijay,

This is working till column 4 , cam you help to build this script till coulmn 8.

Thanks,
AS

• ###### Re: Pivot table Calculation Help

Just a sample,

If(ColumnNo()=1,Sum(Value)/After(Sum(Value),2),

If(ColumnNo()=2,Sum(Value)/After(Sum(Value),1),

If(ColumnNo()=3,Sum(Value)/Sum(Value),

If(ColumnNo()=4,Sum(Value)/Before(Sum(Value),1),

If(ColumnNo()=5,Sum(Value)/Before(Sum(Value),2),

If(ColumnNo()=6,Sum(Value)/Before(Sum(Value),3),

If(ColumnNo()=7,Sum(Value)/Before(Sum(Value),4),

If(ColumnNo()=8,Sum(Value)/Before(Sum(Value),5),

))))))))

I had to change sorting of Location to load order. Also attaching the data I have used.

• ###### Re: Pivot table Calculation Help

Can you provide the sample app?

• ###### Re: Pivot table Calculation Help

Thanks,
AS

• ###### Re: Pivot table Calculation Help

Hi Amit,

Try the following:

Sum(Values)/ Sum(Total <STDDATE> {<Location={"CHESTERFIELD"}>} Values)

• ###### Re: Pivot table Calculation Help

Hi. In expression you mast write

if(Location<>'CHESTERFIELD',sum({<Location-={CHESTERFIELD}>} Value)/sum(TOTAL<STDDATE> {<Location={CHESTERFIELD}>} Value) ,sum(Value) )

• ###### Re: Pivot table Calculation Help

Great! I feel this is better and dynamic solution. I just checked it is working perfectly on the data I used.Thanks!

• ###### Re: Pivot table Calculation Help

Good one , just one issue :

• ###### Re: Pivot table Calculation Help

Looks like this will work:

if(Location<>'CHESTERFIELD',sum({<Location-={CHESTERFIELD}>} Value)/sum(TOTAL<STDDATE> {<Location={CHESTERFIELD}>} Value) ,sum({<Location={CHESTERFIELD}>} Value)/sum(TOTAL<STDDATE> {<Location={CHESTERFIELD}>} Value))

Thanks,

AS

• ###### Re: Pivot table Calculation Help

Oh yeah, this can be corrected by sum(Value)/sum(Value) in else condition of expression.

• ###### Re: Pivot table Calculation Help

I mean in the expression shared by sergeykostin

or you can use the one by

or mine one...:)

• ###### Re: Pivot table Calculation Help

in the 2nd line pl read  - by esuyanti