Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
zagzebski
Creator
Creator

Disregarding selection in text box - aggr function

I have the following expression in a text box that works fine -  with no selections. Basically I want to count the number of customer / line of business combinations that fit the criteria in the set analysis:

Sum( aggr( if(
Sum({1<Year={$(=max(Year)-1)},[Customer Name]=>} [Amount Revenue Production])=0 and Sum({1<Year={$(=max(Year))},[Customer Name]=>} [Amount Revenue Production])>0 and
(
Sum({1<Year={$(=max(Year))},[Customer Name]=>}[Amount Revenue Production])>=5000 or Sum({1<Year={$(=max(Year))},[Customer Name]=>}[Amount Premium])>=5000)
   ,1,0),
[Customer Name],[Line of Business]
))

However when the user selects a customer it changes the result of this expression. I want the number to be the same whether there is a filter on the customer name or there is no filter on the customer name.

Thanks,

Zag

1 Reply
asgardd2
Creator III
Creator III

Hello!

    I think you must add else one Sum() function, delete [Customer Name] from AGGR() and add "{<[Customer name] =]>}" in first Sum() function.

Sum({<[Customer Name]=>}

aggr(

Sum({<[Customer Name]=>} if(
Sum({1<Year={$(=max(Year)-1)},[Customer Name]=>} [Amount Revenue Production])=0 and Sum({1<Year={$(=max(Year))},[Customer Name]=>} [Amount Revenue Production])>0 and
(
Sum({1<Year={$(=max(Year))},[Customer Name]=>}[Amount Revenue Production])>=5000 or Sum({1<Year={$(=max(Year))},[Customer Name]=>}[Amount Premium])>=5000)
  ,1,0)),
[Line of Business] //[Customer Name],
))

Simplified example in attachment.