Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Above() Function Not Working With More Than One Dimension

Sample app attached. I have the above() function working as expected in 1 straight table. I then the same chart copied (then added an additional dimension) and the above() function no longer works. In the help it describes the following limitation:

If the table or table equivalent has multiple vertical dimensions, the current column segment will include only rows with the same values as the current row in all dimension columns except for the column showing the last dimension in the inter field sort order. The inter field sort order for pivot tables is defined simply by the order of the dimensions from left to right. For other chart types this can be manipulated in the Chart Properties: Sort dialog.

I am not sure exactly what this is saying. I tried working with the sort dialog on the 2nd dimension to get the above() to still work but to no avail. The value of my dimensions will change throughout the rows as I am trying to look at Demand (Calc Qty) vs. Supply (On Order) and calculate a projected qty on hand. One row shows demand (work order) and the other supply (purchase order). Any thoughts?

/emb

1 Solution

Accepted Solutions
pover
Luminary Alumni
Luminary Alumni

Just add the TOTAL keyword to rowno() and above() and you'll get it working with multiple dimensions:

if( rowno(total)=1, sum([Other_Parts.Qty On Hand] - [Other_Parts.Safety Stock Qty] - [Calc Qty] + [Purchase Qty]-[Total Received Qty]), above(total column(3)) - Sum([Calc Qty])+ Sum([Purchase Qty]-[Total Received Qty]))

Regards.

View solution in original post

4 Replies
pover
Luminary Alumni
Luminary Alumni

Just add the TOTAL keyword to rowno() and above() and you'll get it working with multiple dimensions:

if( rowno(total)=1, sum([Other_Parts.Qty On Hand] - [Other_Parts.Safety Stock Qty] - [Calc Qty] + [Purchase Qty]-[Total Received Qty]), above(total column(3)) - Sum([Calc Qty])+ Sum([Purchase Qty]-[Total Received Qty]))

Regards.

Not applicable
Author

Perfect... Thanks!

johnw
Champion III
Champion III

Two problems.

First, if you use a simple + or - and a value is null, the result will be null. 5 + null = null. That's making a lot of your results null. In fact, it appears to me that even your TOP table is wrong for this reason. If I understand what you intended, it certainly doesn't appear that you wanted the first line to be 0. It appears that the first line should be:

30.446 Other_Parts.Qty On Hand
-45 -Other_Parts.Safety Stock Qty
-85.179 -Calc Qty
+null +Purchase Qty
-null -Total Received Qty
-------
-99.733 =Desired Total

The second line should be:

-99.733 previous line
-null -Calc Qty
+90 +Purchase Qty
-0 -Total Received Qty
-------
-9.733 =Desired Total

The third line should be:

-9.733 previous line
-0.829 -Calc Qty
+null +Purchase Qty
-null -Total Received Qty
-------
-10.562 =Desired Total

Right?

So the problem is that those nulls are making at least the first line start everything off wrong. You get away with it on the other lines because if you do sum(A), and A is null, the sum is 0, and some of those values above are written as sums instead of raw values. So using sum() is one way to force nulls to be 0, and rangesum() is another common solution. I'll just stick with sum() for now, which means putting every field in its own sum:

if(rowno()=1
,sum([Other_Parts.Qty On Hand])
-sum([Other_Parts.Safety Stock Qty])
-sum([Calc Qty])
+sum([Purchase Qty])
-sum([Total Received Qty])
// else
,above(column(3))
-sum([Calc Qty])
+sum([Purchase Qty])
-sum([Total Received Qty]))

And that produces the results it looks like you intended, at least in the first chart.

The second chart demonstrates your second problem, which is the one you noted, and the one Karl gave the solution for. Expanding on what he said, functions like rowno() and above() work within a column SEGMENT, not a column. A column segment is that portion of a column that has every dimension value the same but the last dimension. It's probably easiest to see this with an example:

D1 D2 rowno() sum(X) above(column(2))
A X 1 1 null
A Y 2 2 1
A Z 3 3 2
B X 1 4 null
B Y 2 5 4
B Z 3 6 5

With your data on the other hand, rowno() is always 1, and above(anything) will therefore always be null.

Fortunately, that's just the DEFAULT behavior of those functions, and you can override it. Specifically, you can use the TOTAL keyword, and then if desired, specify the list of dimensions that you want to use to constrain what's in your column segment. The default behavior for the table above means that rowno() = rowno(total <D1>). In your case, you want the column segment to be the entire table, so you wouldn't put any dimensions in the list after "total". And above() works exactly the same way. So you need to modify the above expression a little further, to this:

if(rowno(total)=1
,sum([Other_Parts.Qty On Hand])
-sum([Other_Parts.Safety Stock Qty])
-sum([Calc Qty])
+sum([Purchase Qty])
-sum([Total Received Qty])
// else
,above(total column(3))
-sum([Calc Qty])
+sum([Purchase Qty])
-sum([Total Received Qty]))

That produces the results I think you want. Let me know if that's not really what you want.

Anonymous
Not applicable
Author

For anyone struggling to get Above() working with 2 dimensions referencing previous Dim2 value - the best way to understand what is going on is converting the chart to a straight table and showing multiple expressions (in my case i needed to show Growth% between years for each value of Dim2).

In my case:

  • Dim1 = Year,
  • Dim2 = Type
  • Sort = Year, Type
  • Expr1 Cnt = Sum(Cnt),
  • Expr2 Previous Value =  Above( Total Sum(Cnt), GetPossibleCount(Type) )
  • Expr3 Above Col1 =  Above( Column(1) )
  • Expr4 Above Total =  Above( Total Column(1) )
  • Expr5 RowNo()
  • Expr6 RowNo(Total)

Data sample:

YearTypeSum(Cnt)Previous ValueAbove Col1
Above TotalRowNoRowNoTotal

2016

A1815---11
2016B966-1815181522
2016Total2781-96696633
2017A27571815-273114
2017B15009662757275725
2017Total425727811500150036

Same data with Sort reversed - i.e. inner Dim first (Type, Year)

YearTypeSum(Cnt)Previous ValueAbove Col1
Above TotalRowNoRowNoTotal

2016

A1815---11
2017A2757-1815181522
2016B966--275733
2017B1500181596696614
2016Total2781966-150025
2017Total425727812781278136

It turns out, Above(Total) refers to the previous row disregarding current dim fields - i.e. in my case - always the wrong value.

So for my case, there are two solutions:

  1. Use natural sort order but use offset param for Above equal to the number of values in the innermost Dim:
    =Above( Total Sum(Cnt), GetPossibleCount(Type) )  , or
  2. Switch sort order to have 2nd dim (Type) first and 1st dim (Year) as second. and use:
    =Above( Column(1) )  - although switch order on the bar/line chart means different chart layout, so most likely not applicable

For 3+ dimensions it would be more complex as you would have to aggregate over remaining dimensions using Aggr() and rangesum();