One of the frequently performed business analysis is identifying the Gap between Highest Sales and 2nd Highest Sales/Revenue/Orders etc.By taking the sample data set above lets perform the Gap analysis in the simplified way using LOD's.
We need to create 2 calculations,to findout 1st Maximum,2nd Maximum and then we need findout the difference between those two.
Create a calculated Field with the name 1st_Max_Score with the following Formula
Create another calculated Field with the name 2nd_Max_Score with the following Formula
Create a calculated Field with the name "Gap" with the following Formula:-
Gap=2nd_Max_Score - 1st_Max_Score