beastmode

NIKKI
NIKKI Member
in Beast Mode

I working on a categorical text dataset with thousand of rows, I have a text column called 'indicatortext' with 21 distinct values , I have another column called 'result' with 7 distinct values, I want to create a table with indicatortext as the row and the percentage occurrence of one of the categories in result column, also the count occurrence of one the categories in result column based on the indicatortext columns(basically how many times, and the percentage occurrence of a categories is appearing for the distinct text in indicatortext column.

I have the below code for percentage COUNT(CASE WHEN `Result` LIKE '%Non-Compliant-Urgent%' THEN 1 END) / COUNT(DISTINCT `IndicatorText`) OVER (PARTITION BY `IndicatorText`) but i am getting zero's as the outcome

this the outcome i trying to get

image.png

Answers

  • MichelleH
    MichelleH Coach

    @NIKKI Do you plan on having a single row per value of the Indicator Text field? If so you do not need to partition by that field in your card because your data will already be grouped by that field. Also, note that your denominator will always be 1 in this case based on your current formula. If that's not what you want, then you can remove the word DISTINCT from your formula to calculate the percentage based off the total number of rows.

  • NIKKI
    NIKKI Member

    Thank you Michelle,

    I tried this COUNT(CASE WHEN Result = 'Non-Compliant-Urgent' THEN IndicatorText END) / COUNT(IndicatorText). but I am still getting zero's populating on my tabel

  • NIKKI
    NIKKI Member

    What I plan on having is the percentange of non-compliant category in result column by the distinct text in indicator text column

  • MichelleH
    MichelleH Coach

    @NIKKI Does the Result column value match 'Non-Compliant-Urgent' exactly?

  • NIKKI
    NIKKI Member

    Yes, I even use wildcard, LIKE %

  • MichelleH
    MichelleH Coach

    @NIKKI Would you be able to share a screenshot of your raw data and beast mode results, hiding any sensitive information?

  • NIKKI
    NIKKI Member

    this is what the raw data looks like.

    image.png

    The first column is the indicator text and the 2nd column is the result with categories.

    this is what the output looks like

    image.png

Categories