How to ignore selection when using Rank and Aggr?

[wenchia]

Hi,

I create a chart that rank the Top Operator of every Region and I only want to show the Top Operator ID.  It's done using Rank and Aggr

aggr(if( aggr(rank(sum({$<CLIENT_REGION_NAME= >}AMOUNT)),CLIENT_REGION_NAME,[OPERATOR ID]) = 1,
avg({$<CLIENT_REGION_NAME= >}[OPERATOR ID])),CLIENT_REGION_NAME,[OPERATOR ID])

My Question is, if I would like to ignore the selection of OPERATOR ID, meaning when someone select an Operator, the chart should not change.  How should I do it?  I tried to put OPERATOR= in the set analysis but it shows only the region which the OPERATOR selected.  I do not what anything change in the chart but couldn't find a solution.

Please assist.

Please find example in the attachment file

[sunny_talwar · Accepted solution]

May be this

Only({<OPERATOR, [OPERATOR ID]>}Aggr(If(Rank(Sum({$<CLIENT_REGION_NAME, OPERATOR, [OPERATOR ID]>}AMOUNT)) = 1,

Avg({$<CLIENT_REGION_NAME, OPERATOR, [OPERATOR ID]>}[OPERATOR ID])), CLIENT_REGION_NAME, [OPERATOR ID]))

[wenchia]

Thank you very much! It works!!

[dirk_fischer]

Hi Sunny

I try to create a calculated dimension and face the same problem as Chia. For some reason, I'm not able to adapt your solution properly to my situation, so I hope you will help me out.

I want to calculate the last 10 days available in my data for an order. So the formula I use for getting the Dates is as below:

Only({<DATE_NO, REP_DATE, ID_DATE={"<$(=Date(Floor(Now(),1),'YYYY-MM-DD'))"}>}

        Aggr({<DATE_NO, REP_DATE, ID_DATE={"<$(=Date(Floor(Now(),1),'YYYY-MM-DD'))"}>} NoDistinct

                 If((Rank(Only({<DATE_NO, REP_DATE, ID_DATE={"<$(=Date(Floor(Now(),1),'YYYY-MM-DD'))"}>}ID_DATE))<=10),

              Dual(ID_DATE,Rank(ID_DATE)),

              Null()

             ),

           (ID_DATE,(NUMERIC,DECENDING))

          )

     )

However, if I select a date, the Dimension is reduced to the selected date and does not show the last 10 days anymore.

Do you have any suggestions, what's wrong?

Thank you very much for your help and best regards,

Dirk

[sunny_talwar]

May be try this

Only({<DATE_NO, REP_DATE, ID_DATE={"<$(=Date(Floor(Now(),1),'YYYY-MM-DD'))"}>}

        Aggr({<DATE_NO, REP_DATE, ID_DATE={"<$(=Date(Floor(Now(),1),'YYYY-MM-DD'))"}>} NoDistinct

                If((Rank(Only({<DATE_NO, REP_DATE, ID_DATE={"<$(=Date(Floor(Now(),1),'YYYY-MM-DD'))"}>}ID_DATE))<=10),

              Dual(Only({<DATE_NO, REP_DATE, ID_DATE={"<$(=Date(Floor(Now(),1),'YYYY-MM-DD'))"}>} ID_DATE), Rank(Only({<DATE_NO, REP_DATE, ID_DATE={"<$(=Date(Floor(Now(),1),'YYYY-MM-DD'))"}>} ID_DATE))),

              Null()

            ),

          (ID_DATE,(NUMERIC,DECENDING))

          )

    )

[dirk_fischer]

Hi Sunny,

thank's a lot. Together with a small modification in my measure, this did the trick.

Best regads,

Dirk