Grouping expression in Qlikview

[#1]

Hi,

I have a requirement in Qlikview and am fairly new to this tool. Any help is appreciated.

I have a straight table with the current view (3 columns)

Cell Offer Desc Sales $

1 A 100

1 B 344

1 C 266

2 D 553

2 E 213

3 F 666

3 G 214

3 H 1982

The user wants to see an updated view which displays only those rows whose Sales is the maximum for each cell.

Updated View:

Cell Offer Desc Sales $

1 B 344

2 D 553

3 H 1982

Once again, the Offer Description used for each cell is that with the highest sales.

I believe this is a simple grouping statement, but not sure how to execute in this tool.

Thanks again![:D]

[#2]

If your expression is Sum(Sales), then try:

If(Rank(Sum(Sales)) = 1, Sum(Sales))

[#3]

Hey,Thanks for your response.

Your method almost did it. It is displaying only the highest sale amt row, but then the other rows are blank and still being displayed.

How do i suppress these rows?

Thanks for your assistance/

[#4]

Is the Offer Desc an expression? If so, then you need to apply the same if to that field. If the third column is the only expression, QlikView would suppress the null values by default. If that's not happening, go to the Presentation tab of Chart Properties and make sure Suppress Missing is checked.

If Offer Desc is an expression, change it to:

If(Rank(Sum(Sales)) = 1, <<Current Offer Desc Expression>>)

[#5]

Thanks again for your assistance..

I know have updated columns:

Year,week,page,cell,offer desc,Sales $

If I use that expression,it is showing the maximum sales by page and not by cell.

Any thoughts? It would be so useful if there was a 'for' expression in Qlikview.

Thanks again..

[#6]

Is Offer Desc an expression? I can't really tell. You describe it like it should be treated as an dimension, but from what you've said, it is behaving as an expression.

If I do Rank(Sum(Sales)) in a test application, QlikView is ranking it by the second to last dimension. If Offer Desc is a dimension, it should be automatically ranking by Cell.

I've attached a sample using your original posted data. When I use Rank(Sum(Sales)), I get ranking by Cell, meaning for each Cell, the ranking stats at 1.

[#7]

Thanks again for being patient with my newbie questions.

Yea,all the dimensions are not expressions.

There is this dimension 'Page' which is giving problems. It is ranking by this dimension.

LOAD * INLINE [
Page,Cell,OfferDesc,Sales
1,1,A,100
1,1,B,344
2,1,C,266
2,1,X,200
2,2,D,553
1,2,Y,240
1,2,E,213
1,3,F,666
1,3,G,214
2,3,H,1982
2,3,I,500
];

[#8]

I get you know, you want the Ranking within each Page. Unfortunately, I'm having some problems. I think it should be something like Rank(TOTAL <Page> Sum(Sales)), but that isn't working.

To get rank within Cell, use:

Rank(Sum(Sales))

To get rank overall, use:

Rank(TOTAL Sum(Sales))

According to the tooltip, Rank(TOTAL <Fields> Expression) should work, but I get null everytime I try. Looking in the Reference Manual, it doesn't look like <Fields> are allowed. Hmm...

[#9 · Accepted solution]

Found it using Aggr(). The Aggr() needs the first and third dimension. Weird.

Aggr(Rank(Sum(Sales)), Page, OfferDesc)

[#10]

Great!!Thank you very much!

In Cognos , using a 'for' operator would pretty much give u the selective grouping..

I just hope QV comes with an easier approach sometime soon..