Ignore selection in Text Object

[#1]

Hi ,

My scenario is, I need to display the No 1 Dealer Name in a text object for the current Quarter and I can do this with the below expression,

=only(if(aggr(Rank((sum({$<BusMonthSeq={"<=$(=Max(BusMonthSeq))"},BusQuartSeq={$(=Max(BusQuartSeq))}>}Income)
/COUNT({$<BusMonthSeq={"<=$(=Max(BusMonthSeq))"},BusQuartSeq={$(=Max(BusQuartSeq))}>}DISTINCT DealID))),Dealer)=1,Dealer))

I also have a chart that displays all my dealers. So if I click on a dealer in the chart, it changes my top dealer text object to the selected dealer.

But this selection needs to be ignored in the text object. Any ideas?

Thanks,

[Oleg_Troyansky]

the problem is in the nested aggregation and in the AGGR function - every time use use AGGR, your Set Analysis condition needs to be repeated in both the inner and the outer aggregations. In your example, the Set Analysis needs to be repeated within the Only() function.

Also, this problem can be solved in a simpler way, without the use of AGGR. Simply use the ONLY() function and a set analysis condition with an advanced search on the field Dealer, that requires that the Rank() is equal to 1. Something like this:

=only( {1< Dealer={'=Rank((sum({1<BusMonthSeq={"<=$(=Max(BusMonthSeq))"},BusQuartSeq={$(=Max(BusQuartSeq))}>}Income)
/COUNT({1<BusMonthSeq={"<=$(=Max(BusMonthSeq))"},BusQuartSeq={$(=Max(BusQuartSeq))}>}DISTINCT DealID)))=1'}

>}

Dealer)

Can't guarantee the exact syntax, but it should be pretty close.

cheers,

Oleg Troyansky

Come and learn Set Analysis and Advanced Aggregation with me at the Masters Summit for QlikView (www.masterssummit.com)

[#3]

Hi,

You have to put dealer field inside set analysis.

Please try the following

---------------------------------------------------------------------------------------------

only(if(aggr(Rank((sum({$<BusMonthSeq={"<=$(=Max(BusMonthSeq))"},BusQuartSeq={$(=Max(BusQuartSeq))}, Dealer=>}Income)
/COUNT({$<BusMonthSeq={"<=$(=Max(BusMonthSeq))"},BusQuartSeq={$(=Max(BusQuartSeq))},Dealer=>}DISTINCT DealID))),Dealer)=1,Dealer))

Regards,

Joshmi

[#4]

Hi Oleg,

Thanks for the reply. I used your expression like below,

=only( {<Dealer={"=Rank(sum({<BusQuartSeq={$(=Max(BusQuartSeq))}>}Income)/COUNT({<BusQuartSeq={$(=Max(BusQuartSeq))}>}DISTINCT DealID))=1"}>}Dealer)

But still had issues with the top dealer chaging when a dealer selection is made in the chart. So I tried setting alernate states to the text box and now it works with your expression.

But now I need to modify the expression for the Value associated with the top dealer. Currently I am using with aggr which will not work

=num(max(Aggr((sum({$<BusQuartSeq={$(=Max(BusQuartSeq))}Income)
/COUNT({$<BusQuartSeq={$(=Max(BusQuartSeq))}DISTINCT DealID)),Dealer)), '$#,##0')

Thanks,

[#5 · Accepted solution]

I chaged the expression like below (added the alternate state) and it works,

=

num(max(Aggr((sum({[AllDlr]<BusQuartSeq={$(=Max(BusQuartSeq))}>}Incomewith)
/COUNT({[AllDlr]<BusQuartSeq={$(=Max(BusQuartSeq))}>}DISTINCT DealID)),Dealer)), '$#,##0')