Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Discussion Board for collaboration related to QlikView App Development.

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Above() Function Not Working With More Than One Di...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

2011-02-28
05:57 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

- Tags:
- above

1 Solution

Accepted Solutions

pover

Luminary Alumni

2011-02-28
07:49 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

4 Replies

pover

Luminary Alumni

2011-02-28
07:49 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2011-02-28
07:55 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Perfect... Thanks!

3,419 Views

johnw

Champion III

2011-02-28
07:57 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2017-02-08
07:51 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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:

Year | Type | Sum(Cnt) | Previous Value | Above Col1 | Above Total | RowNo | RowNoTotal |
---|---|---|---|---|---|---|---|

2016 | A | 1815 | - | - | - | 1 | 1 |

2016 | B | 966 | - | 1815 | 1815 | 2 | 2 |

2016 | Total | 2781 | - | 966 | 966 | 3 | 3 |

2017 | A | 2757 | 1815 | - | 2731 | 1 | 4 |

2017 | B | 1500 | 966 | 2757 | 2757 | 2 | 5 |

2017 | Total | 4257 | 2781 | 1500 | 1500 | 3 | 6 |

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

Year | Type | Sum(Cnt) | Previous Value | Above Col1 | Above Total | RowNo | RowNoTotal |
---|---|---|---|---|---|---|---|

2016 | A | 1815 | - | - | - | 1 | 1 |

2017 | A | 2757 | - | 1815 | 1815 | 2 | 2 |

2016 | B | 966 | - | - | 2757 | 3 | 3 |

2017 | B | 1500 | 1815 | 966 | 966 | 1 | 4 |

2016 | Total | 2781 | 966 | - | 1500 | 2 | 5 |

2017 | Total | 4257 | 2781 | 2781 | 2781 | 3 | 6 |

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:

- 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 - 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();

3,419 Views