topic Re: How to ignore selection when using Rank and Aggr? in QlikView

How to ignore selection when using Rank and Aggr?

wenchia — Wed, 02 May 2018 16:52:17 GMT

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

Re: How to ignore selection when using Rank and Aggr?

sunny_talwar — Wed, 02 May 2018 16:58:39 GMT

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

Re: How to ignore selection when using Rank and Aggr?

wenchia — Wed, 02 May 2018 17:05:38 GMT

Thank you very much! It works!!

Re: How to ignore selection when using Rank and Aggr?

dirk_fischer — Mon, 13 Aug 2018 16:33:32 GMT

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

Re: How to ignore selection when using Rank and Aggr?

sunny_talwar — Tue, 14 Aug 2018 19:46:18 GMT

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

)

Re: How to ignore selection when using Rank and Aggr?

dirk_fischer — Thu, 16 Aug 2018 07:32:21 GMT

Hi Sunny,

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

Best regads,

Dirk