Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
zagzebski
Contributor

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
Contributor III

Re: Disregarding selection in text box - aggr function

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.