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: 
Not applicable

Filtering on Calculated Dimension

Hi,

Just wondering if it is possible to filter on a Calculated Dimension within a straight table?

I have tried clicking on the values in the Calculated Dimension column but have noticed that it instead filters on the other, standard, dimensions in order to make it look like you're filtering on the calculated one. However I want to create a bookmark based on selections made in these Calculated Dimensions, so filtering on other columns won't work as these values may change.

Is it possible to do this at all, or do they just not work in that way?

11 Replies
sunny_talwar

I think it depends on the calculated dimension, What is the dimension that you are using?

Not applicable
Author

It's using a FirstSortedValue function to find the latest value for account records. When I added it in by itself I got the usual error message with calculated dimensions, so i've wrapped it in an AGGR function, which would explain why it then filters on a different field when it's selected

sunny_talwar

Can you copy paste the whole thing here and give an example of what gets selected when you select something in the dimension?

rubenmarin

Hi James, hard to guess without seeing the calculated dimensión expression.

As a tip: usually the filters will be applied to the fields used as dimensions in the Aggr() function, in example:

=Aggr(Max(Field1), Field2, Field3) //Will make selections on Field2 and Field3

Not applicable
Author

Sure. So the expression i'm using for the dimension is:

=Aggr(FirstSortedValue(DISTINCT Owner, -Date), AccountNum)

and the table itself has dimensions of AccountNum and this calculated one, and then a number of expressions.

When I click on the calculated dimension I want to be filtering on the name of the person in the owner field, however what it does instead is filter on the account numbers of the accounts the person is against. This isn't ideal as the bookmark would save the selection of accounts numbers, which would change over time as accounts move owners, rather than the selection of the person who owns the account.

Not applicable
Author

I thought that might be the case when I checked what was actually being applied in the filters. Does it matter what field(s) go into the AGGR function? I assumed it was almost like a grouping statement, but if I can change it to be something else then I should be able to get it to work

rubenmarin

Hi James, the closest I get is:

=Aggr(If(Max(Order)=Max(TOTAL <Acc> Order), Own), Acc, Own)

Checking 'Supress when null'

It will select on both fields: Acc and Own.

I did my test with this sample script:

LOAD * INLINE [
Acc, Own, Order
1, A, 1
1, B, 2
2, A, 1
3, A, 1
3, B, 2
3, A ,3
]
;

sunny_talwar

In this case this dimension also seems to work:

=Aggr(FirstSortedValue(DISTINCT Own, -Order), Acc)

I think it would be best if we can look at a sample file from James

rubenmarin

I think the problem here is that James wants the behaviour of that column as if it was just the 'Owner' field, so when you select a name in the table it keeps all the accounts where that name appears as the current owner.

Althought I didn't get the part of the bookmark, I think he doesn't wants the account to be selected, just the name.