Display N rows by group in straight table

Anonymous — Sat, 10 Jul 2010 20:43:25 GMT

Hi guys,

I've a problem on displaying a subset of rows per group of rows in a straight table.

My straight table has 2 dimensions:

BrandTypology (like Coffee, Garments, Cosmetics...)
Brand (a number of company names producing coffee, garments, cosmetics...)

I need to display all the brand typologies and only some relevant brands.

The Brand expression is the following:

=if(
aggr(sum(wSampleBrand), BrandTypology,Brand) >= 50
and aggr(max(wAffinityBrand),BrandTypology,Brand) >= 110,
Brand
)

The valid brands are those:

with SAMPLE value >= 50
with AFFINITY index >= 110

In this case I end up with a table of brand typologies and way too many different brands associated to the typology and satisfying the filter criteria.

The problem is that in no way do I manage to leave only TOP N brands per BrandTypology.

I've tried using MAX NUMBER but it limits the total number of rows in the table ignoring the BrandTypology dimension. So, unacceptable.

I've tried adding another filter condition to the Brand dimension making it look like this:

=if(
aggr(sum(wSampleBrand), BrandTypology,Brand) >= 50
and aggr(max(wAffinityBrand),BrandTypology,Brand) >= 110
and aggr(rank(wAffinityBrand),BrandTypology,Brand) <= 5,

Brand
)

But the last aggregation does not take into account the previous conditions (it simply keeps assigning ranks to the brands that do not satisfy previous conditions and are therefore already excluded). So, the result is one BrandTypology has 5 rows, another has 3 rows, still another has just 0 ones. And setting the number from 5 to 7 I suddenly discover a new row appear on the third BrandTypology which previously had 0 rows. That's not a correct TOP N functionality.

I've tried rowno() and noofrows() functions but they do not seem working inside the dimension expression.

I've tried above() function but still with the same negative result.

Is there a way to solve the problem?

topic Display N rows by group in straight table in QlikView

Display N rows by group in straight table