topic Re: Iterative Calculation in Qlikview in QlikView

Iterative Calculation in Qlikview

Tue, 01 Sep 2015 06:44:04 GMT

in the image below, Net Profit is calculated as follows;

NetProfit = B1-B2-B3

The field Salaries is calculated

Salaries = 0.1 * B4

This is allowed in excel with the option iterative calculation, as the field B3 is used in both calculations of NetProfit and Salaries.

How can I apply the same in a qlikview application.

Re: Iterative Calculation in Qlikview

marcus_sommer — Tue, 01 Sep 2015 11:13:33 GMT

I don't know how these iterations will be made (probably some kind of random-function which runs x-time and the average will taken) and think they are not really predictable. I personal would prefer a multi-stage margin-calculation like:

Sales

- Material

= Margin 1

- Rents

= Margin 2

- Salaries

= Margin 3

....

- Marcus

Re: Iterative Calculation in Qlikview

hic — Tue, 01 Sep 2015 11:29:09 GMT

If you do it in the script, you can use the Peek() function to address above records. If you do it in a chart in the UI, you can use the Above() function. But you cannot have recursive definitions as you have in your example, where NetProfit is calculated from Salaries, and Salaries is calculated from NetProfit.

Further, in both databases and in QlikView/Qlik Sense, you should usually not store different things in the same column and let the order define what it is. Instead you should have them in different columns and name the columns appropriately. Then it is easy to do what Marcus suggests - which is also how I would do it.

HIC

Re: Iterative Calculation in Qlikview

dinuwanbr — Thu, 03 Sep 2015 06:31:00 GMT

Hi,

PFA

Rgds,

Dinu1

Re: Iterative Calculation in Qlikview

Thu, 03 Sep 2015 06:40:29 GMT

This was just an example for a real time implementation where there are two fields which are calculated recursively. Will try to avoid recursive definitions for sure Thank you

Re: Iterative Calculation in Qlikview

Thu, 03 Sep 2015 06:43:58 GMT

Hi Marcus,

Thanks for the response Will try it out