slicer not working on the beast mode calculation

art_in_sky

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?

Manasi_Panov

Hello @art_in_sky,

Is such a message currently displayed?

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

art_in_sky

@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

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

art_in_sky

@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'

ArborRose

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

art_in_sky

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