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: 
shekhar_analyti
Specialist
Specialist

Why i am not getting same result with Above function ?

Hi All ,

Why i am not getting same result with Above function used in two different expression , below :

1) Sum({1<Year={"=Above(Year)"}>}[Foreign Exchange Reserves in US Dollar - Total])

2) Above([Foreign Exchange Reserves in US Dollar - Total],1)

Thanks & Regards

Shekar

20 Replies
Anil_Babu_Samineni

Haha

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

There are going to be at least 100 things in QlikView which I won't be sure about my friend ... I am just another user like you... the person who might/would know everything would be Henric

shekhar_analyti
Specialist
Specialist
Author

That is your modesty . However you can still not stop me in cherishing the moment , loaded with fact that our "Artillery"  Sunny , is not sure about it .. 

Regards

Shekar 

hic
Former Employee
Former Employee

Interesting... I cannot explain the behaviour completely, but we will look at it.

Jonathan and Sunny are both right when they say that Above() cannot be used like that. The Set Analysis is like a selection and is NOT evaluated row-by-row. So, the expression is incorrect.

What puzzles me is that the expression behaves differently on the first row (where it returns 0) and on the following rows (where it returns a number).

HIC

tresesco
MVP
MVP

I guess the behavior could be explained like below:

The trick is the '=' sign in the set ( '=Above(Year)'). When we put that, set analysis actually gets evaluated for TRUE condition of the field. Like, if you write : Customer={"=Sum(Sales)>0"}, then we are searching for those customers against whom the condition is true; and not for specific values of customers. Similarly, when we write Year={"=Above(Year)"}, it sees if there is an above value for that year (if it has an early/above value then it's a TRUE) or not. And obviously, it's true (present) for all the years except the first one.

If we use below() instead, we would see all the values except the last one.

I am sure that the same (what I am trying to express) could have been explain in a much better way, but hope - what I wrote is conveyed and makes sense.

sunny_talwar

Hey tresesco‌ - Did you look at the image I posted above? Year can never equal to Above(Year)... so shouldn't we always have a FALSE? and the table should not show anything? Am I missing something?

Capture.PNG

sunny_talwar

Oh I see what you mean... we are not doing Year = Above(Year)... it is only doing Above(Year)... that makes sense...

Capture.PNG

So, since row 1 is evaluated as false, we don't see getting number... whereas all others are true...

shekhar_analyti
Specialist
Specialist
Author

Sunny i am little confused . As per your statement

" we are not doing Year = Above(Year)... it is only doing Above(Year) " ......

If somehow we enforce  " Year = Above(Year) " , then will expression work ???

or

as the earlier understanding , we cannot make use of Above function with set expression , whatsoever !!!

hic
Former Employee
Former Employee

tresesco‌ is spot on.

When a search string starts with an equals sign, a temporary one-dimensional cube is created to evaluate the search string. In this case it is a cube with Year as dimension, and Above(Year) as measure. In the first row of the cube, the measure returns NULL (which is interpreted as FALSE) and the following rows get a non-zero number (which are interpreted as TRUE). This array defines which Years the search string picks out.

So the first Year (first according to Load order) is not selected in the Set Analysis expression, but the following Years are.

Bottom line: The QIX engine works as designed!

sunny_talwar

Year = Above(Year) won't work also... like hic‌ has explained below, you cannot use this approach in set analysis.