Creating dynamically calculated fields

[Anonymous]

Hi guys,

I'm trying to recreate an OLAP application in Qlikview.

I have a table of data and some 5 tables with master data. One of these tables is ACCOUNT table which has some 2 thousand members. Around 100 of them are dynamically calculated:

ID Formula
A1
A2
A3
KPI1 A1+A2
KPI2 A3+KPI1
KPI3 KPI1/KPI2
...

In the example above the accounts A1,A2,A3 are the accounts which hold data and KPI1,KPI2,KPI3 are the dynamically calculated accounts (MDX).

So, my question is whether it is possible to create dynamically calculated fields. It would be perfect to have a table Account both with fixed value and dynamic accounts.

I wanted to avoid using variables since I would have to create some 100 variables. Neither can I recalculate these values in the load script since they depend on the context and thus should generate different values for different selections.

Are there any ideas on how to accomplish this?

Thanks.

Regards,

Valera

[Oleg_Troyansky]

You can read your dynamic definitions into a separate table, then process the table "row by row" (you are talking about some 100 definitions, right?) and create a single variable that would hold the portion of your load statement with all the "dynamic" calculations spelled out. You use the variable as a part of your load statement, using $() - expansion.

In other words, you'll need to build a little "interpreter" for your formula definitions. For simplicity, you might want to resolve all the references to other KPIs upfront (for example, spell out KPI1/KI2 in terms of basic components.

good luck!

[Anonymous]

Hi Oleg,

thanks for the suggestion. I was trying to solve the problem exactly in the way you told. 🙂

However, the model is in fact too complicated to use variables:

1) the topmost calculated account depends on around 60 other calculated accounts with an average length of MDX formulas coming to 1000 symbols (meaning that the formulas are unfortunately too complicated and it becomes literally impossible to decompose the topmost calculated account into simple non-calculated accounts)

2) in addition the Account dimension is hierarchical, which means that around 1300 accounts serve as parents and form a complex hierarchy. This adds additional complexity to the model and I can hardly imagine using aggregations on fields and variables simultaneously.

We managed to construct the OLAP application just thanks to the fact that MS Analysis Services support calculated members (accounts based on MDX formulas) and hierarchies. But due to its complexity the calculation of the topmost calculated account takes more than 3 days of time intersected with another dimension of 10000 elements (in fact, it takes some more time, I just had to stop the script execution after 3 days). And the updated values are required nightly. 😕

So, I'm searching a way to recreate the application in Qlikview but it seems even more complicated without the native support of calculated fields...