Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
yipchunyu
Creator
Creator

Filter a dimension based on max value of another field

I created a straight table on the front end want to filter the records by a dimension (based on max value of another value)

=if(FieldA = AGGR(max([FieldA]), [FieldB]), [FieldB])

and then suppress records when is null.

 

It works for most of the records but some of them isn't working

All the dimension fields will be null for these records (if I clicked suppress when value is null).

Any idea?

 

 

8 Replies
asinha1991
Creator III
Creator III

i did not understand your requirement, can you please elaborate preferably with examples?

arpitkharkia
Creator III
Creator III

Sample App/data would help a lot in helping out.

 

yipchunyu
Creator
Creator
Author

Sorry for my poor English.

I have a strange table with 3 dimension fields and some other expression fields

PeopleBranch CodeWorkOrderOther expression fields
A12310 
A12111 
B11112 
C15513 
C16514 
C16015 

 

I want to show the records only with the max(workOrder) by people

PeopleBranch CodeWorkOrderOther expression fields
A12111 
B11112 
C16015 

 

I tried to modify the Branch code field as below

=if([WorkOrder] = AGGR(max([WorkOrder]), [People]), [Branch Code])

and click suppress value when is null. 

Most records are ok but some records are not ok (e.g. missing the people c )

PeopleBranch CodeWorkOrder
A12111
B11112

 

Hope I explained it clear enough this time

 

 

tresesco
MVP
MVP

Use FirstSortedValue() like:

 

Dim : People

Exp1 : FirstSortedValue([Branch Code], - [Work Order])

Exp2 : Max( [Work Order])

asinha1991
Creator III
Creator III

use something like this and see

Dimension:
1. People
2. BranchCode
3. AGGR(max([WorkOrder]), [People]), [Branch Code])

Expressions

yipchunyu
Creator
Creator
Author

Sorry, I tried your method but still can't make it works.
Actually, there are more than 3 dimension fields. I need to apply the same logic which only get the value based on the max(intStaffSeq)
From:
Staff Code Dept Code Staff Status intStaffSeq Expression Fields
00000C0957 39774 00 - Inforce 379722
00000C0957 39775 61 - Terminated 379723
00000C0957 39776 61 - Terminated 379717

To:
00000C0957 39775 61 - Terminated 379723
yipchunyu
Creator
Creator
Author

Still haven't sort it out yet.  Any advice is welcomed.

tresesco
MVP
MVP

FirstSortedValue() would be the way. Try to create and share a sample app where we can try and help you get the solution right.