2 Replies Latest reply on Sep 9, 2015 11:37 PM by 2885278

    Rank in fact table?

    2885278

      Hello Guys

       

       

      I need advises guys

       

      example: I have one table called "FS_DPK_Rank"


      Here is the table looks like:

      fact table.png

      and the Report will like this:

      fact table 2.png

      where Balance Amount will SUM per Product and TOP 2 is as TopN per CIF NO (globally TopN)

       

      but there is condition: when user choose Office then function TopN will adjust per Office.

       

      I'm really confused how to solve this situation.really need suggestions, advises, and solutions.with respectfully

        • 1. Re: Rank in fact table?
          Ebin

          Hi,

           

          I tried your scenario on my local machine and was able to achieve your requirement in a different way. Please find below the data set I tried. I will post it here if I find some other solution.

           

           

          Final Result

           

           

          Monthly Activity Sum - SUM("Activity Facts"."# of Activities" by "Time"."Month")

           

          Sum (Top 2) - SUM((CASE WHEN RANK("Activity Facts"."# of Activities" by "Time"."Month")<3 then "Activity Facts"."# of Activities" else 0 end) by "Time"."Month")

           

           

           

           

          In your Case, replace "Time"."Month" by the grouping column. Also in the above example I have given the rank directly. Replace this with some CASE statement as per your requirement. Also place the CIF NO in the analysis and then exclude this column in the table layout.

           

          Assume you have RPD model in place for this table.

          • 2. Re: Rank in fact table?
            2885278

            @Ebin: I would say thanks a lot..I wil try it,if it wiorks out I will post here and marked Corret Answer.once again a million thanks