1 Reply Latest reply: Mar 9, 2017 8:55 PM by Petter Skjolden

# Calculate specific rows in dimension from load script?

Hi all,

I have a excel file with the following structure (see attached) that I use as a datasource. By sorting the rows this way I get the right calculations and structure in my pivot table. My problem is that in some rows I want to make calculations using the the values from other rows. Is there a way I can accomplish that either in the excel sheet or in the load script?

• ###### Re: Calculate specific rows in dimension from load script?

The calculations can be done either in the load script or very similarly in the charts instead. I have made an example app that does both.

```XL:
If( IsNull(Account) , ' ' , Text(Account)) AS Account,
If( IsNull([Cost center]),' ' , Text([Cost center])) AS [Cost center],
Trim([Calculation wanted]) AS C
(ooxml, embedded labels, table is [Financial report])
WHERE
RecNo()<=21;

// Give each row a name which is the same as the headline
FOR i=1 TO NoOfRows('XL')
[\$(var)] = i-1
NEXT

Account,
[Cost center],
If( RecNo() = 1 , 1 , If( Left( C , 1 ) = '=' , Peek('C#')+1 , Peek('C#') ) )  AS C#,
If( Left(C,1) <> '=' , C , Pick( Peek('C#') ,
RangeSum(Peek('C',\$(Sales)),Peek('C',\$(Net Shipping Revenue)),Peek('C',\$(Other Sales)))
,RangeSum(Peek('C',\$(COGS)),Peek('C',\$(Other COGS)))
,RangeSum(Peek('C',\$(Net Revenue)),Peek('C',\$(Total Cogs)))/Peek('C',\$(Net Revenue))
,Peek('C',\$(Total Cogs))/Peek('C',\$(Sales))
,Peek('C',\$(Net Revenue))+Peek('C',\$(Total Cogs))
,Peek('C',\$(CM1))/Peek('C',\$(Net Revenue))
,RangeSum(Peek('C',\$(Delivery Costs)),Peek('C',\$(Payment Cost)),Peek('C',\$(Pick&Pack Personnel Costs)),Peek('C',\$(Ops Personnel Costs)),Peek('C',\$(Customer Care Personnel Costs)),Peek('C',\$(Memnon)),Peek('C',\$(Material)))
,RangeSum(Peek('C',\$(CM1)),Peek('C',\$(CM2 Cost)))
,Peek('C',\$(CM2 Total))/Peek('C',\$(Net Revenue))
))
AS C
RESIDENT
XL
;

DROP TABLE XL;
```

The calculations in the chart would be as an alternative:

```If( Len([Cost center]) > 1 , Sum(C) , Pick( Above(TOTAL C#)
,RangeSum(Top(TOTAL Sum(C),\$(Sales)+1),Top(TOTAL Sum(C),\$(Net Shipping Revenue)+1),Top(TOTAL Sum(C),\$(Other Sales)+1))
,RangeSum(Top(TOTAL C,\$(COGS)+1),Top(TOTAL C,\$(Other COGS)+1))
,RangeSum(Top(TOTAL C,\$(Net Revenue)+1),Top(TOTAL C,\$(Total Cogs)+1))/Top(TOTAL C,\$(Net Revenue)+1)
,Top(TOTAL C,\$(Total Cogs)+1)/Top(TOTAL C,\$(Sales)+1)
,Top(TOTAL C,\$(Net Revenue)+1)+Top(TOTAL C,\$(Total Cogs)+1)
,Top(TOTAL C,\$(CM1)+1)/Top(TOTAL C,\$(Net Revenue)+1)
,RangeSum(Top(TOTAL C,\$(Delivery Costs)+1),Top(TOTAL C,\$(Payment Cost)+1),Top(TOTAL C,\$(Pick&Pack Personnel Costs)+1),Top(TOTAL C,\$(Ops Personnel Costs)+1),Top(TOTAL C,\$(Customer Care Personnel Costs)+1),Top(TOTAL C,\$(Memnon)+1),Top(TOTAL C,\$(Material)+1))
,RangeSum(Top(TOTAL C,\$(CM1)+1),Top(TOTAL C,\$(CM2 Cost)+1))
,Top(TOTAL C,\$(CM2 Total)+1)/Top(TOTAL C,\$(Net Revenue)+1)
))
```

If you prefer to do calculations in the chart you could remove lines 18 to 39 in the load script.