How to Sum variable?

building reports webintelligence
1

Hi,

I know how to do this in Excel, but Webi makes this challenging.

I have variable that is a measure that counts some codes (ie code 100 is 75 total, code 101 is 52 etc…)

I am making a table below and just want to make a formula that says: If code is 100 or 101 sum that total (so in the example above, it should display 127).

2

=Sum ( [ variable with count of codes ] Where ( [code] InList ( “100”;“101”) )

1 Like
3

Counts and sums are very different things and not typically interchangeable. Are you counting records or summing field values from records?

4

I am trying to sum up a few counted records. So for example if I have 5 apples (the 5 is a counted record) and 6 oranges, I can write a formula to sum apples and oranges to get 11.

Make sense?

Let’s say I have 8 bananas but I don’t want to include those I can still get 11.

5

If you have separate count variables:
v_Apple_DC = count( [RowID] ; distinct ) where( [Fruit] = ‘apple’ )
v_Orange_DC = count( [RowID] ; distinct ) where( [Fruit] = ‘orange’ )
v_AppleAndOrange = [v_Apple_DC] + [v_Orange_DC]

or you can do in one step:
v_AppleAndOrange_DC = count( [RowID] ; distinct ) where( [Fruit] inlist( ‘apple’ ; ‘orange’ ) )

1 Like
6

Thank you @N8AKTIV this did provide me with what I needed. :grinning:

Thanks,

8

Be VERY careful summing counts. Depending on your data and the format of your report, sums of distinct counts can be completely wrong.

Summing distinct counts is a very bad habit to get into!

9

Thanks, I will be careful. I have never had an issue. Once I get a report configured, if I can verify it works, I know I can leave it be and it will generate good data.

This is my trouble with Webi, I often know how to achieve the results I want, but the manner in which I do it may not be clean/the best way… Is there some kind of resources (video tutorials, book, PDF, website…) where I could read to research all this stuff other than struggle my way through it?

10

Think of it this way…

I have a report with customers per store. I do a distinct count of customers who shopped at store A and also have a distinct count of customers who shopped at store B. If I add those two values, what do I have?

If you are looking for the total number of customers at both stores, that might work. But if you are looking for a distinct count of customers, you may luck out and it may be correct if, during this data run, no customer shopped at both stores. But, next time it may not be correct because a customer(s) did shop at both stores and would be counted twice by summing your individual counts.

Store A
Joe
Jim
Bob
Distinct Count = 3

Store B
Joe
Paul
Fred
Marvin
Distinct Count = 4

A + B = 3 + 4 = 7
Distinct Count of Customers = 6 – Joe is in both A and B

Both are potentially correct, it depends on what you are trying to measure and your definition. That’s something you’d need to work out with your users.

SAP has various training videos on their YouTube channel:

Speak BO also has lots of recorded webinars in their library that cover lots of topics - https://www.speakbo.com/. You do have to register to access, but it’s free. Note, I am not affiliated with them.