How to exclude null in a summation?

building reports webintelligence
1

I have a crosstab report with totals across both axes. For some months, I have don’t have a measure in the intersection. I’d like the sum total to show “n/a” if ANY of the months has a null in the intersection. Like this…

------------Jan-------Feb-------Mar-------Sum:
Prod1:----<1>---------<5>------<n/a>
Prod2:----<4>------<2>------<5>------<11>
Prod3:----——<3>------<n/a>
Sum:------<n/a>—<n/a>—<13>-----<n/a>

How can I do this? Currently, my sum row and column aggregate as if null values were zeroes.

Many thanks in advance.

bluesky :us: (BOB member since 2006-03-02)

2

bluesky,

How about if you use the following:

If(IsNull(;Sum()=“N/A”;Sum()

or something similar in your aggregation formula?

Mitra

Mitra Moini (BOB member since 2002-09-01)

3

Thanks for your suggestion, Mitra, but since the (measure) sum object is defined as numeric, it doesn’t allow me to replace w/ a string. I get the invalid data type error.

I tried this similar formula before and it erred out the same way:
=If(isNull;“N/A”;Sum())

Any other ideas?

bluesky :us: (BOB member since 2006-03-02)

4

bluesky,

Is it a null value or just blanks? Because there is a difference. If they are blanks then use the following:

If(=" ";Sum()=“N/A”;Sum())

Mitra

Mitra Moini (BOB member since 2002-09-01)

5

Mitra,

The measure object is numeric, so it’s a null. The error was caught at the “N/A” substitution.

bluesky :us: (BOB member since 2006-03-02)

6

Try this,

Sum=IF(Count([Month] ForEach [Product])=Count([Month] in report);FormatNumber(Sum([Value]);"#");"N/A")

Edit:

The above formula is derived for cross tab structure. The blank/null cells in a cross tabs are virtual place holder which cannot be identified in BO using any formula.

The concept I used is to identify the total number of months in the report (assuming 1 year data) and comparing this count with the count of month each product has value. If both these numbers match then the summation will be shown at the footer else “N/A”.

KhoushikTTT :us: (BOB member since 2005-02-24)

7

Thanks, NewyorkBaasha. I tried your formula but I got an error at the ForEach operator: “Missing or bad calculation context FOR’ at position 31. (Error: WIS 10071).” I don’t think ForEach is allowed w/ the Count function; you can only use IncludeEmpty and Distinct|All.

When I took off the ForEach clause, my results shows “N/A” for all sum values, whether or not a null exists in the crosstab…rightfully, so, I guess. :?

bluesky :us: (BOB member since 2006-03-02)

8

Take a look at the attachment.
Cross_tab.wid (25.0 KB)

KhoushikTTT :us: (BOB member since 2005-02-24)

9

I’m sorry. I’ve never imported a .wid file before. :oops: Would you mind showing me the steps? I’ve tried using Import/Migration/Publishing Wizards, but none seems to work w/ this file type.

bluesky :us: (BOB member since 2006-03-02)

10

Do you have the BOXI 3.1 Rich Client installed ?

If Rich Client is available, Download, save it to your desktop and open the report(.wid) via Rich Client.
The Zip file also contains the Screen shot .doc and the formula used.
Crosstab.zip (20.0 KB)

KhoushikTTT :us: (BOB member since 2005-02-24)

11

Unfortunately, we’re still using XI R2. I don’t have 3.1 Rich Client. Is there any way you can show me a screen shot? I’m really sorry to trouble you, but I’m very anxious to see your solution.

Thanks so much!

bluesky :us: (BOB member since 2006-03-02)

12

Check the Attachment (crosstab.zip) in my previous post .
This compressed file has the actual report, screen shot of the report and the formula used.

KhoushikTTT :us: (BOB member since 2005-02-24)

13

Sorry. You are absolutely correct. You can not substitue a string for a number. How about if you try the function FormatNumbers as below:

If(IsNull();FormatNumber(Sum();“N/A”);Sum())

Mitra

Mitra Moini (BOB member since 2002-09-01)

14

A change to the formula for across footer.

Down Footer of cross tab

Body of Cross-Tab =Count([Account])
Actual Count=Count(Count([Account]))
Count to check values for all month=Count([Product]) In Report
Expected Aggregation =If(Count(Count([Account]))=Count([Product]) In Report;Count(Count([Account]));"N/A")

Across Footer of cross tab

Body of Cross-Tab =Count([Account])
Actual Count=Count(Count([Account]))
Count to check values for all month=Count([Product]) In Report
Expected Aggregation =If(Count(Count([Account]))=Count([Month]) In Report;Count(Count([Account]));"N/A")

Crosstab.zip (19.0 KB)

KhoushikTTT :us: (BOB member since 2005-02-24)

15

Mitra - That still didn’t work. NewyorkBaasha got the answer for me. Thanks anyway. :slight_smile:

NewyorkBaasha - What brilliance! I only had to tweak it a bit to get it working my way (ie, switched “In Report” to “In Section” since my report has crosstabs sectioned by Regions and not all sections/regions have the same number of Products). I also figured out the Across Footer formula on my own but am glad you just validated it for me. Thanks again!

I’ve really learned a lot from this forum. Thank you all for taking the time to help us out!

bluesky :us: (BOB member since 2006-03-02)