Why i am not getting same result with Above function ?

[shekhar_analyti]

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

[Anil_Babu_Samineni]

Haha

[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]

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]

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 · Accepted solution]

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?

[sunny_talwar]

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

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

[shekhar_analyti]

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]

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.