"Above" function - not working

[jdgraham]

I'm trying to do what should be a very simple task for the "Above" function.  I have a table with very few columns.  It would look something like:

DATE                      NBR

xx                            5

xx1                           4

I want to use the Above function to put in a new column with the difference and then calculate the percent of change.

It would be from above 5 minus the current row which is 4  -   5-4=1

Above formula should be something like

Above(NBR)-NBR   (this would be taking the 5 to minus the 4)

If it is 

NBR-Above(NBR)    (this would be taking the 4 and minus the 5)

It will not work either way.  Produces a column with nothing but " - " in it. 

What is the correct process for this?

[rubenmarin]

Hi, I did a test and it returns the 1:

The row returning "-" could be the first, as it doesn't have any more "Above" records. 

Maybe your tabel has more dimensions, in that case you can use: Above(TOTAL NBR)-NBR.

Can you post an example that shows the issue?

[Greg_Taffer]

The Above function in Qlik is failing likely because the table is not properly sorted, or because the expression requires an aggregation (like Sum) to function correctly within the table's context. A "-" usually indicates a NULL value in Qlik Sense, which happens on the first row because there is no row above it. 

Here is the correct process to calculate the difference and percent change.

1. The Correct Formulas (As Measures)

Assuming your dimension is DATE and your measure is NBR: 

• Difference: Sum(NBR) - Above(Sum(NBR))
• Percent Change: (Sum(NBR) - Above(Sum(NBR))) / Above(Sum(NBR)) 

2. Why it's returning " - " (Null)

• No Data Above: The very first row in your table will always show a NULL (-) because there is no row above it to compare to.
• Sorting Issue: Above() works on the displayed table sorting. If your dates aren't sorted chronologically, the "above" row is arbitrary.
• Missing Aggregation: If NBR is a field that appears multiple times for the same date, Above(NBR) will return null. You must use Above(Sum(NBR)). 

3. Step-by-Step Solution

1. Add Dimensions: Create a table with DATE and NBR.
2. Sort Properly: Go to Sorting in the property panel. Ensure DATE is sorted Ascending (earliest to latest).
3. Add Measure (Difference):
   • Label: Difference
   • Expression: =Sum(NBR) - Above(Sum(NBR))
4. Add Measure (% Change):
   • Label: % Change
   • Expression: =(Sum(NBR) - Above(Sum(NBR))) / Above(Sum(NBR))
   • Set Number Formatting to "Percentage". 

4. Handling the First Row Nulls

If you want to avoid NULL or the hyphen, use RangeSum to treat nulls as 0, or Alt to replace it: 

// Returns 0 instead of '-' if there is no previous value =RangeSum(Sum(NBR) - Above(Sum(NBR)))

5. Troubleshooting (If the above fails)

If you have multiple dimensions (e.g., Year, Month) or Above still returns -, use the TOTAL keyword to ignore the inner dimension sorting: 

• =Sum(NBR) - Above(TOTAL Sum(NBR))