GetFieldSelections on calculated dimensions null values

[Anonymous]

With thanks to

calculated dimensions & getfieldselections and  Inline load & null values for getting me to the point of being able to ask the question correctly.

In Qlik Sense, you can create your own dimensions in tables. For example, I have a dimension in data, where data in the dimension is NULL, as a result of Qlik internal joins.

I'd like to calculate some content based on the selection in that dimension, however, if I turn the dimension into a calculated dimension to make the NULLs more pretty (or indeed, some other grouping), GetFieldSelections() doesn't treat the calculated selection as valid, even though there is a 1:1 correlation.

I'm assuming that this is by design, and intended behaviour, but I'm not sure how you would use GetFieldSelections() on that calculated dimension without creating the field specifically in the data load, joining up all your data manually or the equivalent

For example:

Set NullInterpret = '';

Books:
load * inline
[
BookID, BookName, title
1, 'a', 'Hi'
2, 'b', 'Go'
3, 'c', 'As'
4, 'd', 'Zoo'
,'e', 'Fred'
];

Creating a table with columns, "BookID", "if(isnull(BookID),'NA', BookID)", "Count(BookName)", I have three text fields showing:

* GetFieldSelections(BookID)

* GetSelectedCount(BookID)

* GetPossibleCount(BookID)

Selecting values in the "BookID" column works as expected, as well as GetPossibleCount() when selecting the calculated dimension (disregarding the Null() field, due to Null() behaviour)

[swuehl · Accepted solution]

I think I haven't really understood your issue.

You should get the same results in your text boxes regardless if your selecting from BookID column or the calculcated dimension column, both should select values only in BookID.

Do you see something different?

The field functions GetFieldSelections(), GetSelectedCount(), GetPossibleCount() work on fields, not on calculated dimensions (I think you know this). So I am not sure why you expect GetFieldSelections( BookID) to return something when you try to select 'NA' in the calculated dimension (you'll notice that you can't select this value alone, just because it's not based on a field value: NULL is not a value or even stored in the symbol table).

[Anonymous]

Sorry, you're right I was going a bit too complicated!

I would like to know if it's possible to use GetFieldSelections() or equivalent functions on calculated dimensions. I think that this specific case is too simple to use for all calculated dimensions, and if I wanted to use these functions directly I should fix the underlying data.

[Anonymous]

So, after ignoring this for a while, and having the problem return, essentially the problem is that there's no specific "GetPossibleValues" function - it's instead hidden and you need to reference the field itself.

So, for my example, I use:

$(=CONCAT(DISTINCT CHR(39) & <my field name> & CHR(39), ','))

to give me a comma-separated list of values (what I was trying to achieve with GetFieldSelections). From here, I can test logic using the MATCH() function, so for example, to see how many times the (text) letter A appears:

match('A', $(=concat(distinct chr(39) & tCREDIT_RATING & chr(39), ',')))