Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi. In expression you mast write
if(Location<>'CHESTERFIELD',sum({<Location-={CHESTERFIELD}>} Value)/sum(TOTAL<STDDATE> {<Location={CHESTERFIELD}>} Value) ,sum(Value) )
I read Before and After functions which works only for Pivot table few days back, will give a try now.
Can you provide the sample app?
Please see the attachment
Thanks,
AS
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)))))
Digvijay,
This is working till column 4 , cam you help to build this script till coulmn 8.
Thanks,
AS
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.
Hi. In expression you mast write
if(Location<>'CHESTERFIELD',sum({<Location-={CHESTERFIELD}>} Value)/sum(TOTAL<STDDATE> {<Location={CHESTERFIELD}>} Value) ,sum(Value) )
Great! I feel this is better and dynamic solution. I just checked it is working perfectly on the data I used.Thanks!
Hi Amit,
Try the following:
Sum(Values)/ Sum(Total <STDDATE> {<Location={"CHESTERFIELD"}>} Values)