Forum Discussion

ErickRuiz7's avatar
ErickRuiz7
Cloud Apps
05-28-2024
Solved

SUM based on Text Criteria

I have a STRING column called Question, this question column provides two possible answeres, either 'Yes; or 'No'

How do I create formulas to sum the amount of times a Yes shows up, the amount of times a No shows up and the amount of times that both of them show up?


Dashboards & Reporting
  • HamzaJ's avatar
    HamzaJ
    05-29-2024

    Hey ErickRuiz7 

    You could use a measure filter to achieve this. Let's assume we have 2 columns; question and answer. A formula would look like this:

    (Count([Question]), [Answer])

    Now you can click on answer and it will show the option Filter. Click on that and you can set the desired filter. This will make the aggregation (in this case a count) count the number of questions for which the answer is , for example, Yes. 

    This can be used in more advanced scenario's. In more traditional scenario's you could add Answer as a widget filter. Or add it to the row/column (pivot table) or for example the break-by in a chart. This will automatically list all found values and counts by it

    Hamza

5 Replies

  • HamzaJ's avatar
    HamzaJ
    Data Integration
    05-29-2024

    Hey ErickRuiz7 

    You could use a measure filter to achieve this. Let's assume we have 2 columns; question and answer. A formula would look like this:

    (Count([Question]), [Answer])

    Now you can click on answer and it will show the option Filter. Click on that and you can set the desired filter. This will make the aggregation (in this case a count) count the number of questions for which the answer is , for example, Yes. 

    This can be used in more advanced scenario's. In more traditional scenario's you could add Answer as a widget filter. Or add it to the row/column (pivot table) or for example the break-by in a chart. This will automatically list all found values and counts by it

    Hamza

    • ErickRuiz7's avatar
      ErickRuiz7
      Cloud Apps
      05-29-2024

      Thanks for the suggestion, I tried it and partially work, but I forgot to mention that the question can repeat several times, I have a total of 6 different questions and a total of 950 answers to each question, the approach is conting only 6, do you have any suggestion for me?

      • TriAnthony's avatar
        TriAnthony
        Admin
        06-10-2024

        Hi ErickRuiz7,

        Sounds like you just need to use DUPCOUNT, so the formula should be: (DupCount([Question]), [Answer])

        If this still doesn't provide the result you need, could you send example values of all the relevant columns? A screenshot of the desired result would be really helpful as well. Lastly, could you also elaborate on in what kind of situations both Yes and No could show up as an answer and how that is reflected in the database?

         

  • david-h's avatar
    david-h
    Cloud Apps
    05-29-2024

    Hi Erick, not sure I understand exactly the use case you are on, but it seems that you could use multi pass agregation (kind of adding a group by in an intermediate calculation in your measure). You may also use this old IT trick : yes=1 and no=0. Let us know if this can be useful in your use case, and if not, maybe you can share a siplified data model.

    Best,

    David.

    • ErickRuiz7's avatar
      ErickRuiz7
      Cloud Apps
      05-31-2024

      Thanks for answering my question, I did the calculation on the elastic cube using a CASE when, I didn't find a way to do a calculation directly on the dashboard.