The problem of changing the number of rows in the above function

[ozlemari]

Hi, I am using above function in table chart. For example, I want the 10th row value to come to the 2nd row in the table. I also do this with the above function.
With the above function, I say return 8 lines of the following value.

Example: IF([line]=2nd, (ABOVE(Sum({<[FirmNumber]={223}>}-Price),-8)

But when I filter in the report, the number of rows in the table changes and the difference between these two rows is 9.7,6,5 etc. instead of 8. it can happen. That is, the value of n changes when the month filter is given. (The rows that do not have a price in the relevant month disappear.)

I also choose the option to get null values in the table chart, but when I give a filter, it doesn't work either.
Is this problem solvable? Can you help me?

[Colleenlazo]

---

@ozlemari wrote:

Hi, I am using above function in table chart. For example, I want the 10th row value to come to the 2nd row in the table. I also do this with the above function.
With the above function, I say return 8 lines of the following value.

Example: IF([line]=2nd, (ABOVE(Sum({<[FirmNumber]={223}>}-Price),-8)

But when I filter in the report, the number of rows in the table changes and the difference between these two rows is 9.7,6,5 etc. instead of 8. it can happen. That is, the value of n changes when the month filter is given. (The rows that do not have a price in the relevant month disappear.)

I also choose the option to get null values in the table chart, but when I give a filter, it doesn't work either.    prepaidgiftbalance
Is this problem solvable? Can you help me?

---

Yes, the problem is solvable. you can adjust your function to dynamically calculate the difference in rows based on the applied filters. Instead of a fixed value like -8, you can use a formula that calculates the difference between the target row and current row dynamically. This way the correct number of rows will be considered even the table is filtered. 

[wmbalance]

It seems like you are trying to create a table chart in QlikView or Qlik Sense where you want to calculate the difference between values in different rows while considering filters applied to the report. The challenge you're facing is that when you apply filters, the number of visible rows changes, affecting the calculation of the difference.

To address this issue and ensure that the calculation considers only the visible rows after filtering, you can use the TOTAL qualifier in QlikView or the TOTAL function in Qlik Sense. This allows you to perform calculations across all visible rows, regardless of the filters applied.

Here's how you can modify your expression:

qlikviewCopy code

IF([line]='2nd', Sum({<[FirmNumber]={223}>}-Price) - ABOVE(TOTAL Sum({<[FirmNumber]={223}>}-Price), 8))

In the above expression:

• TOTAL is used to calculate the sum across all visible rows, ensuring that the calculation considers only the rows that are currently displayed in the chart after applying filters.

This should help you achieve the desired result where the calculation takes into account only the visible rows in the table chart, even when filters are applied.

Please adjust the syntax as needed to match the specific QlikView or Qlik Sense version you are using, as there may be slight differences in syntax between versions.