Forum Discussion

Jennifer2018's avatar
Jennifer2018
Regular Visitor
8 years ago

Percentile Category

Hi,

 

I am trying to categorize my percentile as below but kept on coming with value of Q4.

 

Can you please check what is wrong?

 

Thanks :)

 

PercentileCategory =
//find cutoff points for profit margin quartiles
VAR LowerMiddle = PERCENTILEX.INC(ALLSELECTED([AHT], 0.25)
VAR Middle = PERCENTILEX.INC (ALLSELECTED([AHT], 0.50)
Var UpperMiddle = PERCENTILEX.INC (ALLSELECTED([AHT], 0.75)
RETURN
//do nested if to set the category
IF([AHT] < LowerMiddle , "Q1",
IF([AHT] < Middle , "Q2",
IF([AHT] < UpperMiddle , "Q3",
IF([AHT] >= UpperMiddle, "Q4",
"NA"))))

5 Replies

    • Jennifer2018's avatar
      Jennifer2018
      Regular Visitor
      8 years ago

      Hi

       

      Thanks for the reply, sorry if it was confusing.

       

      Here is a sample data. Note AHT is a measure and there is a date slicer and branch slicer so AHT value changes depending on period and branch selected.

       

      BranchMonth-YearAHTPctileCatg
      aFeb-18435.1Q4
      bFeb-18768.5Q4
      cFeb-18393.2Q4
      dFeb-18415.7Q4
      eFeb-18603.3Q4
      fFeb-18459.1Q4
      gFeb-18395.4Q4
      hFeb-18430.8Q4
      iFeb-18672.5Q4
      jFeb-18584Q4
      kFeb-18448.2Q4
      lFeb-18520.1Q4
      mFeb-18576.3Q4
      nFeb-18410.9Q4
      oFeb-18420.1Q4
      pFeb-18324.2Q4

       

      My end result is hopefully a bar graph of Pctile category and branch.

       

      Appreciate any help. Thanks.

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        8 years ago

        Thanks Jennifer2018. I believe what you want is a column like this:

         

        PercentileCategory = 
//find cutoff points for profit margin quartiles
VAR LowerMiddle = PERCENTILEX.INC(ALL(Percentiles),[AHT],.25)
VAR Middle = PERCENTILEX.INC (ALL(Percentiles),[AHT],0.50)
Var UpperMiddle = PERCENTILEX.INC (ALL(Percentiles),[AHT],0.75)
RETURN
//do nested if to set the category
IF([AHT] < LowerMiddle , "Q1",
IF([AHT] < Middle , "Q2",
IF([AHT] < UpperMiddle , "Q3",
IF([AHT] >= UpperMiddle, "Q4",
"NA"))))

        Note that "Percentiles" is my table and it is the one you supplied minus the last column. I get varying values for this column so I believe it is correct. Here is my output:

         

        Percentiles (table)

        Branch    Month-Year                           AHT        PercentileCategory (the formula above)

        aSunday, February 18, 2018435.1Q2
        bSunday, February 18, 2018768.5Q4
        cSunday, February 18, 2018393.2Q1
        dSunday, February 18, 2018415.7Q2
        eSunday, February 18, 2018603.3Q4
        fSunday, February 18, 2018459.1Q3
        gSunday, February 18, 2018395.4Q1
        hSunday, February 18, 2018430.8Q2
        iSunday, February 18, 2018672.5Q4
        jSunday, February 18, 2018584Q4
        kSunday, February 18, 2018448.2Q3
        lSunday, February 18, 2018520.1Q3
        mSunday, February 18, 2018576.3Q3
        nSunday, February 18, 2018410.9Q1
        oSunday, February 18, 2018420.1Q2
        pSunday, February 18, 2018324.2Q1

         

        I'm actually a little surprise you were getting anything, there seemed to be syntax errors in the formula you posted.