topic Re: Rank Aggr issue (Top impact) in QlikView

Rank Aggr issue (Top impact)

Anonymous — Tue, 14 Nov 2017 14:01:30 GMT

Hi,

I'm in need of some help. I have a Straight table that shows Gross Margin impact this year compared to last in percent by product area. The Expression is a sum aggr on one dimension. My problem is that I want to show the top 3 Impact based on if the negative Impact is the biggest then I want the top 3 negative Impact to be shown and if the positive is the biggest I want the 3 top positive to show. Needs to be dynamical, the result can be different on different weeks. I tried the Rank function but haven’t got it to work. I past some example, How the table could look and what I want it to show.

Regards,

Ellinor

Re: Rank Aggr issue (Top impact)

Anil_Babu_Samineni — Mon, 20 Nov 2017 03:29:36 GMT

Perhaps this?

Sum({<[Product Area] = {"=Rank(Sum(Impact)) <= 3)"}, Week = {'$(=Max(Week))'}>} Impact)

Re: Rank Aggr issue (Top impact)

sunny_talwar — Mon, 20 Nov 2017 11:35:02 GMT

My problem is that I want to show the top 3 Impact based on if the negative Impact is the biggest then I want the top 3 negative Impact to be shown and if the positive is the biggest I want the 3 top positive to show.

Based on the data you have provided, can you explain what do you mean by biggest negative and biggest positive impact here?

Re: Rank Aggr issue (Top impact)

sunny_talwar — Mon, 20 Nov 2017 11:51:34 GMT

Here are two expressions based on my understanding of your requirement.... These will work when a week is selected

1) =If(Rank(Only(TOTAL Aggr(If(Rank(fabs(Impact)) = 1, Sign(Impact)), ProductArea))*Impact) < 4, Impact)

2) Only({<ProductArea = {"=Rank(Only(TOTAL Aggr(If(Rank(fabs(Impact)) = 1, Sign(Impact)), ProductArea))*Impact) < 4"}>}Impact)