A different formula for each table cell

googel84 — Mon, 03 Nov 2014 14:43:58 GMT

Hi there, I am facing a quite challenging issue.

We have to display 70 KPIsin a single sheet and each of them is (almost always) split across 4 columns (LYTD, YTD, Target, Target vs YTD), i.e. there roughly are 280 actual KPIs ! To be more explicit, the expected result is a table made of 70 rows and 6 columns (2 dimensions - KPI group and KPI - and 4 expressions). This means that, each table cell has to evaluate a different formula based on its row and column.

We managed to organize all the formulas in a single Excel file whose structure is very close to the one of the expected pivot table and where each KPI is identified by a code (RTA measure index). I have attached a revised copy of this file.

After many trials and errors, I have reached the result by using for each expression a very similar formula based on pick and fieldvalue (instead of if and fieldvalue), but the performance is considered poor... (30 s on a 2.4 GHz dual core, 12 GB, dedicated machine).

pick( [RTA_FORMULE.RTA measure index],

num($(=fieldvalue('RTA_FORMULE.RTA measure formula 2',1)),'$(vRTAMeasureAAA_F_2)'),

num($(=fieldvalue('RTA_FORMULE.RTA measure formula 2',2)),'$(vRTAMeasureAAB_F_2)'),

num($(=fieldvalue('RTA_FORMULE.RTA measure formula 2',3)),'$(vRTAMeasureAAC_F_2)'),

num($(=fieldvalue('RTA_FORMULE.RTA measure formula 2',4)),'$(vRTAMeasureAAD_F_2)'),

...

num($(=fieldvalue('RTA_FORMULE.RTA measure formula 2',70)),'$(vRTAMeasureACR_F_2)'),

)

What could be used in order to speed up the performance in this scenario?

I have actually tried a few things such as using dynamically created variables to host each of the 280 formulas and I was actually able to place in each cell the right variable name but I haven't been able to proceed with the next step, i.e. interpreting variables' content as a formula and evaluate it to get the right numbers. Enable the last expression to see what I mean.

Please have a look to the attached qvw. Sorry for its size, but the original file could not be easily filtered and then scrambling made it even bigger..

Many thanks in advance.

Re: A different formula for each table cell

googel84 — Tue, 04 Nov 2014 11:09:08 GMT

Just a quick additional question: provided that we find the way to efficiently select the right formula for the right cell, the table still has to perform a different calculation for each of these cells; does this mean that performance can't really be optimized?

topic Re: A different formula for each table cell in QlikView

A different formula for each table cell

Re: A different formula for each table cell