topic Re: Aggregation Function in if statement in QlikView

Aggregation Function in if statement

Mon, 08 Sep 2014 19:32:19 GMT

Hello everyone,

Could somebody let me know why

this expression is working perfect :

if(GetSelectedCount(NamePOS)=1,NamePOS,
if(GetSelectedCount(NamePOS)>1 and GetSelectedCount(NamePOS)< 13 , 'Multiple Accounts','All Accounts'

But when I cheange the # 13 to count ({<Year = {'2014'}>}distinct NamePOS) so the expression now is

if(GetSelectedCount(NamePOS)=1,NamePOS,
if(GetSelectedCount(NamePOS)>1 and GetSelectedCount(NamePOS)< count({<Year = {'2014'}>}distinct NamePOS) , 'Multiple Accounts','All Accounts'))

It does not work?

The expression I am puting in the culculated Label name for expression in a bar chart.

Please help

Thanks in advance.

Re: Aggregation Function in if statement

JonnyPoole — Mon, 08 Sep 2014 20:34:25 GMT

With the aggregation function you MIGHT need the first 'then' wrapped in an aggregation function as well , in this case only(). If that doesn't work i would like to look at your sample QVW if you are able to share.

f(GetSelectedCount(NamePOS)=1, only(NamePOS)
if(GetSelectedCount(NamePOS)>1 and GetSelectedCount(NamePOS)< count({<Year = {'2014'}>}distinct NamePOS) , 'Multiple Accounts','All Accounts'))

Re: Aggregation Function in if statement

jerem1234 — Mon, 08 Sep 2014 20:35:43 GMT

try this:

if(GetSelectedCount(NamePOS)=1,NamePOS,
if(GetSelectedCount(NamePOS)>1 and GetSelectedCount(NamePOS)< count({1<Year = {'2014'}>}distinct NamePOS) , 'Multiple Accounts','All Accounts'))

(I added the 1 to set analysis in the count function if you don't see it)

If you don't have the one, it will count how many NamePOS's that are currently selected (or is possible). In your case it will be the same as GetSelectedCount and the second condition will evaluate to false when a selection is made.

Hope this helps!

Re: Aggregation Function in if statement

Mon, 08 Sep 2014 20:51:48 GMT

HI Jeremiah,

That works thank you very much, but I have not exactly understood what is the purpose of one.

I see you are trying to explain that in your replay but I did not get it.

Could you expalin it to me again?

Thank you

Re: Aggregation Function in if statement

jerem1234 — Mon, 08 Sep 2014 21:01:29 GMT

Hi Denis,

1 will ignore all selections you have made in your fields.

Here is a snippet from qlikview help:

Set Identifiers

There is one constant that can be used to denote a record set; 1. It represents the full set of all the records in the application.

The $ sign represents the records of the current selection. The set expression {$} is thus the equivalent of not stating a set expression. {1-$} is all the more interesting as it defines the inverse of the current selection, i.e. everything that the current selection excludes.

Selections from the Back/Forward stack can be used as set identifiers, by use of the dollar symbol: $1 represents the previous selection, i.e. equivalent to pressing the Back button. Similarly, $_1 represents one step forward, i.e. equivalent to pressing the Forward button. Any unsigned integer can be used in the Back and Forward notations, i.e. $0 represents the current selection..

Finally, bookmarks can be used as set identifiers. Note that only server and document bookmarks can be used as set identifiers. Either the bookmark ID or the bookmark name can be used, e.g. BM01 or MyBookmark. Only the selection part of a bookmark is used. The values are not included. It is thus not possible to use input fields in bookmarks for set analysis.

Examples:

sum( {$} Sales )
returns sales for the current selection, i.e. the same as sum(Sales).

sum( {$1} Sales )
returns sales for the previous selection.

sum( {$_2} Sales )
returns sales for the 2nd next selection, i.e. two steps forward. Only relevant if you just made two Back operations.

sum( {1} Sales )
returns total sales within the application, disregarding the selection but not the dimension. If used in a chart with e.g. Products as dimension, each product will get a different value.

sum( {1} Total Sales )
returns total sales within the application, disregarding both selection and dimension. I.e. the same as sum(All Sales).

sum( {BM01} Sales )
returns sales for the bookmark BM01.

sum( {MyBookMark} Sales )
returns sales for the bookmark MyBookMark.

sum({Server\BM01} Sales)
returns the sales for the server bookmark BM01.

sum({Document\MyBookmark}Sales)
returns the sales for the document bookmark MyBookmark.

Hope this helps!!

Re: Aggregation Function in if statement

Mon, 08 Sep 2014 21:13:45 GMT

O I got it!

And thank you very much for the information it is very helpful!

Have a good week.