topic Grouping expression in Qlikview in QlikView

Grouping expression in Qlikview

Mon, 01 Nov 2010 19:41:27 GMT

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]

Grouping expression in Qlikview

Mon, 01 Nov 2010 19:44:32 GMT

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

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

Grouping expression in Qlikview

Mon, 01 Nov 2010 20:15:20 GMT

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/

Grouping expression in Qlikview

Mon, 01 Nov 2010 20:29:19 GMT

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

Grouping expression in Qlikview

Wed, 03 Nov 2010 16:07:16 GMT

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

Grouping expression in Qlikview

Wed, 03 Nov 2010 16:31:48 GMT

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.

Grouping expression in Qlikview

Wed, 03 Nov 2010 18:02:24 GMT

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

Grouping expression in Qlikview

Wed, 03 Nov 2010 19:52:27 GMT

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

Grouping expression in Qlikview

Wed, 03 Nov 2010 19:59:08 GMT

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

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

Grouping expression in Qlikview

Wed, 03 Nov 2010 21:27:43 GMT

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