slicer not working on the beast mode calculation

art_in_sky
art_in_sky Contributor
in Beast Mode

I have a beast mode calculation with a CASE statement , where it considers another beast mode calculation to indicate high, low and medium risks. However , when used it as a slicer, it only displays 'no risk'and does not indicate high, low and medium in the slicer options. I am using it to filter my HTML table based on a rating score.

How can I achieve this?

Answers

  • Manasi_Panov
    Manasi_Panov Contributor

    Hello @art_in_sky,

    Is such a message currently displayed?

    image.png

    If so, please apply sorting and Change Aggregation settings from here.

    image.png

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

  • art_in_sky
    art_in_sky Contributor
    edited December 2024

    @Manasi_Panov

    Not that, I am using the below calculation
    total rating=
    SUM(
    CASE
    WHEN Leverage Ratio values >= 61.00 THEN 1
    WHEN Leverage Ratio values >= 40.00 and Leverage Ratio values<= 60.00 THEN 2
    WHEN Leverage Ratio values >= 0.00 and Leverage Ratio values<= 39.00 THEN 3
    ELSE 0
    END
    +
    CASE
    WHEN Cash on hand values <= 3.00 THEN 1
    WHEN Cash on hand values >= 4.00 AND Cash on hand values <= 6.00 THEN 2
    WHEN Cash on hand values >= 7.00 THEN 3
    ELSE 0
    END
    +
    CASE
    WHEN Profit/Loss Margin values < 0.00 THEN 1
    WHEN Profit/Loss Margin values >= 0.00 AND Profit/Loss Margin values <= 9.00 THEN 2
    WHEN Profit/Loss Margin values >= 10.00 THEN 3
    ELSE 0
    END
    +
    CASE
    WHEN Current Occupancy <= 85.00 THEN 1
    WHEN Current Occupancy >= 86.00 AND Current Occupancy <= 95.00 THEN 2
    WHEN Current Occupancy >= 96.00 THEN 3
    ELSE 0
    END
    +
    CASE
    WHEN Active % of beds >= 51.00 THEN 1
    WHEN Active % of beds >= 26.00 AND Active % of beds <= 50.00 THEN 2
    WHEN Active % of beds >= 0.00 AND Active % of beds <= 25.00 THEN 3
    ELSE 0
    END
    +
    CASE
    WHEN LHC Strength >= 5.00 THEN 3
    WHEN LHC Strength >= 3.00 AND LHC Strength <= 4.00 THEN 2
    WHEN LHC Strength <= 2.00 THEN 1
    ELSE 0
    END
    )

    which is a numeric value for each row, now i am using the below beast mode calculation using the above ( it is the above best mode 'total rating' instead of invalid reference

    CASE
    WHEN INVALID_REFERENce >= 0 AND INVALID_REFERENCE <= 6 THEN 'High Risk'
    WHEN INVALID_REFERENCE >= 7 AND INVALID_REFERENCE <= 12 THEN 'Medium Risk'
    WHEN INVALID_REFERENCE >= 13 AND INVALID_REFERENCE <= 18 THEN 'Low Risk'
    ELSE 'No Risk'
    END

    I am using slicer to show the high risk, medium risk and low risk to filter the table rows based on the above calculation, however i only see 'no risk ' in the options.

  • ArborRose
    ArborRose Coach

    Try using an ETL instead of beast mode. Create a total rating calculation column that computes the sum logic at the dataset level. Then create a beast mode calculation as a silcer.

    CASE
    
  WHEN `total_rating` >= 0 AND `total_rating` <= 6 THEN 'High Risk'
    
  WHEN `total_rating` >= 7 AND `total_rating` <= 12 THEN 'Medium Risk'
    
  WHEN `total_rating` >= 13 AND `total_rating` <= 18 THEN 'Low Risk'
    
  ELSE 'No Risk'
    
END

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • art_in_sky
    art_in_sky Contributor

    @ArborRose I tried creating 'total rating' calculation at Dataset level in Magic ETL, However , it throws the below error 'Aggregate functions are not allowed in this context'

    image.png
  • ArborRose
    ArborRose Coach

    You are still attempting to aggregate in a beast mode calculation. I was assuming a group by tile to aggregate.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • art_in_sky
    art_in_sky Contributor

    @ArborRose This worked when I created separate formulas instead of aggregating at Dataset level and applied the aggregation in beast mode.

Categories