topic Re: Ignore selection in Text Object in QlikView

Ignore selection in Text Object

Fri, 06 Feb 2015 03:22:50 GMT

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,

Re: Ignore selection in Text Object

Oleg_Troyansky — Fri, 06 Feb 2015 03:59:25 GMT

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)

Re: Ignore selection in Text Object

Fri, 06 Feb 2015 09:19:36 GMT

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

Re: Ignore selection in Text Object

Fri, 06 Feb 2015 15:50:10 GMT

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,

Re: Ignore selection in Text Object

Fri, 06 Feb 2015 16:12:22 GMT

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')