Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to filter top 1 sales regarding dimension in straight table

Hi everyone ,

I have 3 dimensions and two expressions in my straight table ,

Category     Sub-Category          Item      Sale     Rank

     A                    a                    a1          5          2

     A                    a                    b1          6          1

     A                    b                    a1          4          1

     B                    a                    a1          4          1

     B                    b                    a1          8          1

     B                    b                    b1          7           2  

     C                    a                    a1          3          1

     C                    b                    a1          5          1

     C                    b                    b1          4          2

     D                    a                    a1          3          1

     D                    b                    a1          2          1

     D                    c                    a1          3          1

Here sales are grouped by Category , Sub-Category and Item, What I want to show only top 1 sales within each subcategory (i.e. with Rank 1),

How can i do that , desired output is given below

Category     Sub-Category          Item      Sale     Rank

     A                    a                    b1          6          1

     A                    b                    a1          4          1

     B                    a                    a1          4          1

     B                    b                    a1          8          1  

     C                    a                    a1          3          1

     C                    b                    a1          5          1

     D                    a                    a1          3          1

     D                    b                    a1          2          1

     D                    c                    a1          3          1

1 Solution

Accepted Solutions
Sokkorn
Master
Master

Hi,

First try this:

Untitled.png

2. And if not working then use

If(Aggr(Rank($(Sale)),Category,Sub-Category ,Item)=1,Aggr(Rank($(Sale)),Category,Sub-Category ,Item))

3. Or

Only(If(Aggr(Rank($(Sale)),Category,Sub-Category ,Item)=1,Aggr(Rank($(Sale)),Category,Sub-Category ,Item)))

Regards,

Sokkorn

View solution in original post

9 Replies
Sokkorn
Master
Master

Hi,

Aggr(Min(Rank),Item) for last expression should work.

Regards,

Sokkorn

Not applicable
Author

Hi sokkorn ,

Of course I can filter data in an expression using above expression , but I dont want to add an extra expression I just want to show records with rank 1 and hide others within straight table (visually).

Sokkorn
Master
Master

Hi mkhaliq13,

What is your Rank expression? Can you share me/

Rgds,

Sokkorn

Not applicable
Author

Hi ,

Here is my rank expression .

Aggr(Rank($(Sale)),Category,Sub-Category ,Item)

Sokkorn
Master
Master

Hi,

What about this

If(Aggr(Rank($(Sale)),Category,Sub-Category ,Item)=1,Aggr(Rank($(Sale)),Category,Sub-Category ,Item),Null())

Regards,

Sokkorn

Not applicable
Author

Well this outputs

Category     Sub-Category          Item      Sale     Rank

     A                    a                    a1          5          -

     A                    a                    b1          6          1

     A                    b                    a1          4          1

     B                    a                    a1          4          1

     B                    b                    a1          8          1

     B                    b                    b1          7           - 

     C                    a                    a1          3          1

     C                    b                    a1          5          1

     C                    b                    b1          4          -

     D                    a                    a1          3          1

     D                    b                    a1          2          1

     D                    c                    a1          3          1

But I want these rows hidden in straight table like this

Category     Sub-Category          Item      Sale     Rank

     A                    a                    b1          6          1

     A                    b                    a1          4          1

     B                    a                    a1          4          1

     B                    b                    a1          8          1 

     C                    a                    a1          3          1

     C                    b                    a1          5          1

     D                    a                    a1          3          1

     D                    b                    a1          2          1

     D                    c                    a1          3          1

Sokkorn
Master
Master

Hi,

First try this:

Untitled.png

2. And if not working then use

If(Aggr(Rank($(Sale)),Category,Sub-Category ,Item)=1,Aggr(Rank($(Sale)),Category,Sub-Category ,Item))

3. Or

Only(If(Aggr(Rank($(Sale)),Category,Sub-Category ,Item)=1,Aggr(Rank($(Sale)),Category,Sub-Category ,Item)))

Regards,

Sokkorn

Not applicable
Author

Hi ,

Well I have fixed this by following way ,

I have created a calculated dimension with following expression :
If(Aggr(Rank($(Sale)),Category,Sub-Category ,Item)=1,Aggr(Rank($(Sale)),Category,Sub-Category ,Item),Null())

then I checked Suppress when value is null. It gives me the desired output , thanks Sokkorn for your kind response.

Sokkorn
Master
Master

Hi,

That great to hear this. Can you close this post?

Cheer!!!

Sokkorn