Showing results for 
Search instead for 
Did you mean: 

Sum based on OR condition

7 - Data Storage
7 - Data Storage


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:



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?



8 - Cloud Apps
8 - Cloud Apps

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. 

10 - ETL
10 - ETL

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?




[email protected]

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

9 - Travel Pro
9 - Travel Pro

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!

Community Team Member
Community Team Member

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.

David Raynor (DRay)