Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

need help with the expression ...

Hi All,

Thanks for your time. I have the following scenario and got stuck with the expression.

1). I use a text box to show the rank of an analyst based on the sum of a metric...

How it works is...

1).We select at least 1 analyst from the list box - for the selected analyst i get the sum and rank then apply the selected analyst EmplloyeeID= using a variable

2)There are other filters like year, quarter, month, LOB, Etc... when 1 value is selected (it is for 1 value selected in the listbox only ) the expression should consider the value selected in the list box say for ex: Year and the SUM and rank should be for the selected analyst and for the selected year, etc for quarter, month, LOB

i cannot get this expression to work...could someone please help  - I have been using 1 in the set modifier to get all the records because i have to get the sum and rank for the analyst based on the dataset of the total analysts

then when selecting the year i need to filter for year and so on for the other filters.. if i take out the 1 from set modifier

the analyst rank is wrong as it will always show 1

SUM( {<EmployeeID={"A633400"}>} AGGR(RANK(Sum({ < [Analyst Name]=, [Analyst Name] -= {"=isnull([Analyst Name]) "} , REDUCTION={"EQUITY"}, Action = {"Opened Report"}>} ReadCounter),4),[Analyst Name]))

From the below list of filters: Will select 1 value from the analyst and it should give the analyst rank considring the sum and rank of the analysts - analyst rank among the group of analysts...

then if i select the year i should apply the filter for the  year selected for that analyst so the rank generally goes down as we are filtering to small dataset....hopefully someone can help

please let me know if this is  not clear

 

 

Capture1.PNG

 

 

1 Solution

Accepted Solutions
Highlighted

This should exclude true nulls (but not white spaces which look like nulls)

[analyst name] = {"*"}

View solution in original post

5 Replies
Highlighted

Can you try this

Sum({<EmployeeID={"A633400"}>} Aggr(
  Rank(Sum({<[Analyst Name] = {"*"}, REDUCTION = {"EQUITY"}, Action = {"Opened Report"}>} ReadCounter), 4)
, [Analyst Name]))
Highlighted
Creator II
Creator II

Hi Sunny, thanks a lot for your time,

when i look at the sum and rank data for the analyst i have nulls which is ranked so to exclude the null i had to use

 [analyst name] -= {"=isnull([analyst name"])}  in the expression  - if we use [analyst name] ={"*"} - will it exlcude the null

or do i have to use both exclude null and * - again thanks a lot

 

 

 

Highlighted

This should exclude true nulls (but not white spaces which look like nulls)

[analyst name] = {"*"}

View solution in original post

Highlighted
Creator II
Creator II

Thank you Sunny,

testing.. thanks a  lot for your time

Highlighted
Creator II
Creator II

Thanks a lot Sunny.