Disregarding selection in text box - aggr function

zagzebski — Sun, 16 Apr 2017 22:35:02 GMT

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

Re: Disregarding selection in text box - aggr function

asgardd2 — Mon, 17 Apr 2017 07:50:34 GMT

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.

topic Disregarding selection in text box - aggr function in QlikView

Disregarding selection in text box - aggr function

Re: Disregarding selection in text box - aggr function