Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
elie_issa
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
OmarBenSalem

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
agigliotti
Partner - Champion
Partner - Champion

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

OmarBenSalem

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

elie_issa
Creator II
Creator II
Author

can you please give me an example.

OmarBenSalem

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

elie_issa
Creator II
Creator II
Author

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.

OmarBenSalem

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

swuehl
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.

elie_issa
Creator II
Creator II
Author

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 ?

elie_issa
Creator II
Creator II
Author

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.