Forum Discussion

vatojavier's avatar
vatojavier
Cloud Apps
03-14-2024

Sum based on OR condition

Hi.

I'm trying do a simple sum based on OR condition of 2 column of a table. For now I just want to display a number (that later I will use for a more complex widget) by summing the elements of the table that satisfies a string condition of a column and a number condition of another column.

So, I want to go from this simple SQL query:

 

 

select
sum(case when level = 'disabling' or coalesce(problem_issue_id, 1) = 48  then tickets else 0 end)
from fact_tickets f

 

 

The closest I could get is by setting this formula withing the widget:
([Total tickets], [level], [problem_issue_id]). In which I filtered [level] to be just "disabling" and [problem_issue_id] to be 48.

The problem is that this formula is setting a AND condition, when I press "Analyze SQL query" button I get this:

 

 

How can set that AND condition to be an OR? Is this even the correct path to do this?

Thanks!

dashboards & reporting

4 Replies

Replies have been turned off for this discussion
  • rapidbisupport's avatar
    rapidbisupport
    Data Pipeline
    03-15-2024

    Hi vatojavier,

    Interesting problem - we could Inclusion–exclusion principle - Wikipedia to convert our ANDs to ORs given a count of items in sets.

    Sum(a OR b) = Sum(a) + Sum(b) - Sum(a AND b)

    I think this should work:

     (([Total tickets], [level]) + ([Total tickets], [problem_issue_id]))

    - ([Total tickets], [level], [problem_issue_id])

    Let me know how you go?

    Thanks,

    Daniel

    RAPID BI

    [email protected]

    RAPID BI - Sisense Professional Services | Implementations | Custom Add-ons

  • MikeGre's avatar
    MikeGre
    ETL
    03-21-2024

    Hi vatojavier ,

    You can always try the flag & filter approach.

    If your goal is to calculate the sum of tickets you should flag them first, by the condition you wish and then sum/count/aggregate them as you wish.

    Based on what you provided above, try this : 

    Hope it helps!

  • GSSC's avatar
    GSSC
    Cloud Apps
    03-14-2024

    I haven't tested this but you might be able to split the formula into separate calculations for each scenario and sum them together:

    ([Total tickets], [level], [problem_issue_id]) + ([Total tickets], [level], [problem_issue_id]) + ([Total tickets], [level], [problem_issue_id])

    Calc 1: level = "disabling", problem_issue_id <> 48

    Calc 2: level <> "disabling", problem_issue_id = 48

    Calc 3: level = "disabling", problem_issue_id = 48

     

    Somebody else might be able to give a cleaner solution if this doesn't work. 

  • DRay's avatar
    DRay
    Admin
    03-22-2024

    Hello vatojavier,

    Have any of the suggestions offered been able to answer your question?

    If so, please click the 'Accept as Solution' button so that other users with the same questions can find the answer faster. If not, please let us know so that we can continue to help.

    Thank you.