topic Re: Why i am not getting same result with Above function ? in QlikView

Why i am not getting same result with Above function ?

shekhar_analyti — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Why i am not getting same result with Above function ?

stabben23 — Wed, 10 Jan 2018 11:16:14 GMT

maybe change here?

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

0 instead of 1

Re: Why i am not getting same result with Above function ?

jonathandienst — Wed, 10 Jan 2018 11:17:19 GMT

I doubt that the Above() inside the set expression is working at all. Above() is a chart function, while the set expression is executed before the chart is constructed. So the Above() function has no context and will probably return null.. The set expression will fail silently (ie be ignored) so your first expression will be effectively:

Sum({1} [Foreign Exchange Reserves in US Dollar - Total])

Sum([Foreign Exchange Reserves in US Dollar - Total])

Re: Why i am not getting same result with Above function ?

stabben23 — Wed, 10 Jan 2018 11:39:39 GMT

sorry, misunderstand the problem

Re: Why i am not getting same result with Above function ?

Anil_Babu_Samineni — Wed, 10 Jan 2018 11:40:47 GMT

You want to see which one?

Re: Why i am not getting same result with Above function ?

shekhar_analyti — Wed, 10 Jan 2018 12:12:47 GMT

Hi Jonathan ,

So you mean to say that , Above function should not be used with set expression as an ideal case . Please correct me , if i got it wrong .

Re: Why i am not getting same result with Above function ?

shekhar_analyti — Wed, 10 Jan 2018 12:14:10 GMT

Hi Anil ,

I wanted to know that , what is wrong with my first expression .

Re: Why i am not getting same result with Above function ?

sunny_talwar — Wed, 10 Jan 2018 12:18:46 GMT

I agree with jontydkpi‌... I don't think Above is even working.... I would have thought for it to show no result at all... but it seems that the set analysis is ignored.... the reason I though it would not work is because if you create a chart with Year as dimension and Above(Year) as expression... you would see that Year is never equal to Above(Year)

Since the set analysis you have used is a search string... only those years where Year = Above(Year) should be included... but that never happens... so nothing should be included...

But that is not what happens, it appears as though the complete year set analysis part is completely ignored... don't know why.... may be hic‌ can tell why

Re: Why i am not getting same result with Above function ?

shekhar_analyti — Wed, 10 Jan 2018 12:27:04 GMT

If someone like Sunny is not sure , then think of my situation . .
Anyways i am glad that , there is finally something about which Sunny is not sure ... ..

Re: Why i am not getting same result with Above function ?

Anil_Babu_Samineni — Wed, 10 Jan 2018 12:28:29 GMT

Interest Question

Re: Why i am not getting same result with Above function ?

Anil_Babu_Samineni — Wed, 10 Jan 2018 12:28:44 GMT

Haha

Re: Why i am not getting same result with Above function ?

sunny_talwar — Wed, 10 Jan 2018 12:31:57 GMT

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

Re: Why i am not getting same result with Above function ?

shekhar_analyti — Wed, 10 Jan 2018 12:41:24 GMT

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

Re: Why i am not getting same result with Above function ?

hic — Wed, 10 Jan 2018 12:49:31 GMT

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

Re: Why i am not getting same result with Above function ?

tresB — Wed, 10 Jan 2018 13:16:49 GMT

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.

Re: Why i am not getting same result with Above function ?

sunny_talwar — Wed, 10 Jan 2018 13:22:24 GMT

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?

Re: Why i am not getting same result with Above function ?

sunny_talwar — Wed, 10 Jan 2018 13:24:33 GMT

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...

Re: Why i am not getting same result with Above function ?

shekhar_analyti — Wed, 10 Jan 2018 13:36:38 GMT

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 ???

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

Re: Why i am not getting same result with Above function ?

hic — Wed, 10 Jan 2018 13:38:23 GMT

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!

Re: Why i am not getting same result with Above function ?

sunny_talwar — Wed, 10 Jan 2018 13:41:40 GMT

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