Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

Filter Issue while using ABOVE Function

hi all,

Please i am having problem with the below expression while filtering on a specific business unit

above(FirstSortedValue(distinct [Business Unit],-[Assignment SDate]))

This expression is working fine in case we are not filtering any business unit.

The purpose of my expression is to get the previous business unit.

Any idea how to solve this issue.

Thanks.

1 Solution

Accepted Solutions
Partner
Partner

This is what I have :

Capture.PNG

Last : =aggr(FirstSortedValue({< BusinessUnit>}distinct BusinessUnit,-AssignementDate),EmployeeNumber)

Before Last : aggr( above(FirstSortedValue({< BusinessUnit>}distinct BusinessUnit,-AssignementDate)) ,EmployeeNumber,AssignementDate)

Capture.PNG

If I select SCTT :

Capture.PNG

View solution in original post

12 Replies
Partner
Partner

if you use FirstSortedValue function in a chart you can use SetExpression.

Partner
Partner

PLEASE, share mock data and explain the desired output with or without a selection.

Creator II
Creator II

can you please give me an example.

Partner
Partner

above(FirstSortedValue({< [Business Unit]>}distinct [Business Unit],-[Assignment SDate]))

Creator II
Creator II

Hi Omar,

This is an example i will share with u :

I have an employee who was working in SREG and then transferred to SCTT (Capture Attached)

The table i have in my dashboard should show the latest status of the employee so I am getting this employee in SCTT but in the same row i need to present the previous Business Unit which is in this example SREG ( Capture 1 Attached)

The problem is if we filter to see only the employees in a specific Business Unit for example here in SCTT automatically the previous Business Unit will show Null which is a bit logical cz the record of SREG is dismissed when choosing SCTT. (Capture 2 Attached)

Any solution for this issue.

N.B : we are facing the same case on the security level. For example if we give a user access only on SCTT the previous Business Unit field will be always null.

Partner
Partner

Will it be possible to share a mock app?

Or maybe construct a simple INLINE table or an excel file containing just this example with data like urs while sharing the expressions u're using?

Sometimes, I need to build ur own example and perform some tests to come back to u with the correct answer.

I'll be waiting

MVP
MVP

Would it maybe be a solution to calculate the previous BU in the script, adding the value to a new field associated with the employee? This will also solve your section access issue, while a chart based solution (which I can think of) would not.

Creator II
Creator II

above(FirstSortedValue({< [Business Unit]>}distinct [Business Unit],-[Assignment SDate]))


This solution is working very well.


We still have the problem in the security cz our security is done by business unit.

will try to solve it and if we didn't found any solution i think i have to send u a mock app but this will take some time to adjust it. Is it helpfull if i opened the app without data and send it to you ?

Creator II
Creator II

I was thinking of such solution but in my case the business unit is found in organization table while all the employee data is in people table so i am not able to generate it in a simple way in the script.

you might give me an idea from your point of view.