Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Grouping expression in Qlikview

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]

1 Solution

Accepted Solutions
Not applicable
Author

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

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


View solution in original post

9 Replies
Not applicable
Author

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

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


Not applicable
Author

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/

Not applicable
Author

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


Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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


Not applicable
Author

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

Not applicable
Author

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

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


Not applicable
Author

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