2 Replies Latest reply on Jan 25, 2019 3:08 AM by Aathithya Kuppusamy

    Return Top 1st Value, 2nd Value and 3rd Value, Using 3 Calculated Fields Respectively

    Aathithya Kuppusamy

      Hi Friends,

                As you see in the Image, I have Bg_Type & Count of Bug_Id fields.

      Untitled.png

      The Challenge is i need to display only the Top 1st  Value (here 635) using a Calculated Field.

      Likewise Only Top 2nd Value (494) & Top 3rd Value (276) using different Calculated Fields.

       

      I tried using RANK.

      Untitled.png

      Untitled.png

      But it is giving the Total count of BUG_Id. Not only the count of BUG_Id for Top 1st Bg_Type.

      Please Help!

       

      Regards

      ~Aathithya

        • 1. Re: Return Top 1st Value, 2nd Value and 3rd Value, Using 3 Calculated Fields Respectively
          Naveen B

          Hi Aathithya,

           

          Since you are using Table calculation you should use Bg Type in Detail and Compute the rank calc using Bg Type then only your calculation will work but if i am right this approach wont work you need to go with Fixed LOD calc and write calculation for

           

          1st max:

          MAX({Fixed [Bg Type]:Count([Bg BUG ID])})

           

          2nd Max:

          MAX(IF SUM({Fixed [Bg Type]:Count([Bg BUG ID]))} <> 1st max

          THEN

          {Fixed [Bg Type]:Count([Bg BUG ID])}

          ELSE
          NULL

          END
          )

           

          3rd Max:

          MAX(IF SUM({Fixed [Bg Type]:Count([Bg BUG ID]))} <> 1st max

          AND SUM({Fixed [Bg Type]:Count([Bg BUG ID]))} <> 2nd Max

          THEN

          {Fixed [Bg Type]:Count([Bg BUG ID])}

          ELSE
          NULL

          END
          )

           

          This will do try this and let me know if there is any issues.

           

          If it helps.

          Kindly mark this answer as correct or helpful to close the thread

           

          BR,

          NB

          • 2. Re: Return Top 1st Value, 2nd Value and 3rd Value, Using 3 Calculated Fields Respectively
            Aathithya Kuppusamy

            Hi Naveen,

                       Thanks for Replying. Sadly 2nd MAX & 3rd MAX are Not Working!

             

            Reason:

            In your Calculation, Both

            SUM({Fixed [Bg Type]:Count([Bg BUG ID]))}  and  1st max  are Aggregated.

            But after THEN '{ Fixed [Bg Type]:Count([Bg BUG ID]) } ' is Not Aggregated.

            So it was throwing an Error- 'Cannot Mix Aggregate and Non-Aggregate Expressions'.

             

            Hence i changed the Format to,

            ( To make it easy, i saved { Fixed [Bg Type]:Count([Bg BUG ID]) } as FIXED )

             

            IF SUM([FIXED]) <> 1st max

            THEN

            MAX([FIXED])

            ELSE

            NULL

            END

             

            Sum( [FIXED] ) is total sum of all Bg_Types which is 2180.

            1st max is Top 1st Value which is 635.

            Hence both are not equal and it is giving the MAX ( [FIXED] ) which is same 635.

            Not Working!

             

            Regards

            ~Aathithya