Sum based on OR condition

[vatojavier]

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!

[GSSC]

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. 

[rapidbisupport]

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]

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!

[DRay]

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.