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

Hi Friends,

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

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.

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

Regards

~Aathithya

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

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.

BR,

NB

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

Hi Naveen,

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

Reason:

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