9 Replies Latest reply: May 31, 2013 3:22 AM by Sokkorn Cheav RSS

    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

        • Re: How to filter top 1 sales regarding dimension in straight table
          Sokkorn Cheav

          Hi,

           

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

           

          Regards,

          Sokkorn

            • Re: How to filter top 1 sales regarding dimension in straight table

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

                • Re: How to filter top 1 sales regarding dimension in straight table
                  Sokkorn Cheav

                  Hi mkhaliq13,

                   

                  What is your Rank expression? Can you share me/

                   

                  Rgds,

                  Sokkorn

                    • Re: How to filter top 1 sales regarding dimension in straight table

                      Hi ,

                      Here is my rank expression .

                       

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

                        • Re: How to filter top 1 sales regarding dimension in straight table
                          Sokkorn Cheav

                          Hi,

                           

                          What about this

                           

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

                           

                          Regards,

                          Sokkorn

                            • Re: How to filter top 1 sales regarding dimension in straight table

                              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