Excel based formula in QlikView

[apoorvasd]

Hello Everyone,

I have a question. Say, I want to create an expression, "ABCD" in my pivot table and the formula for calculating the expression is as shown in the attached image. Is it possible to write this kind of formula in QlikView which refers to values of next rows while calculating the value for that particular row.

I have attached the sample file also.

Thank you.

[apoorvasd]

Any suggestion on this?

[marcus_sommer]

In general you could access other rows and columns within the pivot with interrecord-functions like above(), below(), before() ... but you will always need to define which row/column should be accessed related from the calling row/column which meant to define the proper offset of cells.

As far as there is a rather simple logic to determine the offset it worked well and otherwise it will be quite difficult. You will never be so flexible in QlikView or any database like in excel because it always needs to be calculable. Therefore if the offset isn't rather simple you will need to calculate/transform your data within the script to your requirements.

Beside them if I look on your example it might be possible to calculate the columns I and K with the help of a total-statement like: sum(TOTAL <Week> AnyValue): What does the TOTAL qualifier do?

- Marcus

[apoorvasd]

Hi Marcus,

Thank you very much for your suggestion!

I tried using "below" function in the expression, but this works only when I select one Plant (like KBK in above excel sheet). When more than two plants are selected or none are selected then the expression doesn't work. Any suggestion?

[AOP Inventory monthly LC]/(below(I)/below(K))

[marcus_sommer]

Your below-function has no second and third parameter to define the offset and therefore it's using as default-parameters just 1 and 1 and returning the values from one row below. Like above mentioned its not easy to define this offset if there isn't a simple logic or even a fixed offset. Qlikview and in general all databases aren't designed to solve data-queries in this way - it's quasi already an exception to the normal way of building a datamodel and to calculate results within the UI.

This meant you might be able to get it working with a complex offset-calculation or even within a combination of some helping rows/columns and some total-calculations but it's far away from easy and performant and also the usability may suffer. Therefore try to solve it within the script or at least to transfer the most complex parts for it there.

- Marcus