Improve performance on pivot table calculation

[Anonymous]

Hi,
I've build a pivot table that calculates an impact between different versions. So I have the main table and Benchmark table (Both are created in the data loader stage). When I select in filter the main version it calculates the impact between all other versions by this formula:

if(RowNo(),
(Avg([duration])-avg( [Benchmark.duration]))/avg( [Benchmark.duration]),
(sum(aggr(avg(duration),[Benchmark.step_name], [Benchmark.agent_version])) - sum(aggr(avg([Benchmark.duration]),[Benchmark.step_name], [Benchmark.agent_version])))/sum(aggr(avg([Benchmark.duration]),[Benchmark.step_name], [Benchmark.agent_version]))
)

The table is aggregated with steps, so I calculate each step separately and in the title row I calculate the sum total of the steps.

Here how it looks in the GUI:

The problem is that it takes way too long to calculate this table. Is there a way to improve the calculation time with better formula or at the data loader stage?

Thanks.

[marcus_sommer]

In general you should try to avoid aggr() functions especially multiple ones within of if-loops.

I'm not sure if it will be possible in your case but I think you could remove the if(rowno(), part and use the expression for the totals also for the single rows - you might need some adjustments on this but often it works.

- Marcus