Forum Discussion

ThomasPHT's avatar
ThomasPHT
Regular Visitor
6 years ago

Create Very Low. Low, Medium, High, Very High Indicators

Hello Everyone, 

 

I try to create indicator based on quintile. ie for a specific zip code try to compare the penetration of 35 - 40 years old vs. entire population 

 

What I did : 

1. create a column to calculate 35-40 years old penetration

2. Create 5 quintiles as measures at 20%, 40%, 60%, 80% based on entire database 35-40 years old penetration

 

try to do 

IF function less where postal code level penetration is less than the 1st quintile apply Very low indicator, if between 1 and 2 quintile, low, 2nd and 3rd , medium, between 3rd and 4th quintile, High, if higher than 4th quintile, Very High

 

Can you help ? 

 

Thank you 

 

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    6 years ago

    You can use a switch statement for that

     

    SWITCH(TRUE(),Percentile.Inc(your data,0.2),"Very low",Percentile.Inc(your data,0.4),"Low",Percentile.Inc(your data,0.6),"Medium,",Percentile.Inc(your data,0.8),"High","Very high")

     

    CAVEAT:  this will only show the data in the "Low" quintile that is not ALSO in the "Very low"  quintile. This may or may not be what you want.

    • ThomasPHT's avatar
      ThomasPHT
      Regular Visitor
      6 years ago

      Hello thank you for your help, 

       

      unfortunately it doesn't work, here is the message:

      Feedback Type:
      Frown (Error)

      Timestamp:
      2020-06-18T13:52:53.3880067Z

      Local Time:
      2020-06-18T09:52:53.3880067-04:00

      Session ID:
      3875600a-d3e9-4486-9f66-9d978a9ec00c

      Release:
      June 2020

      Product Version:
      2.82.5858.641 (20.06) (x64)

      Error Message:
      MdxScript(Model) (38, 285) Calculation error in measure 'Sample _data'[Quintile 2 test]: Function 'SWITCH' does not support comparing values of type True/False with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values.

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI ThomasPHT ,

     

    PlYou can try categorising.

     

    Category =
    SWITCH(
    TRUE(),
    Quartiles[Att%] < Quartiles[Percentile.25] , "Top Quartile",
    (Quartiles[Att%] >= Quartiles[Percentile.25]) && (Quartiles[Att%] < Quartiles[Percentile.5]) , "25th to 50th Quartile",
    (Quartiles[Att%] >= Quartiles[Percentile.5]) && (Quartiles[Att%] < Quartiles[Percentile.75]) , "50th to 75th Quartile",
    "Bottom Quartile"
    )
     

    Regards,
    Harsh Nathani

    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)