BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Sunset Ride 
Sunset Ride 

General Notice: BOB is going to retire...please see details here. All BOB forums have been converted to "read only" as of September 1st.
General Notice: No events within the next 45 days.

Calculation Context - count v countAll, dims v measures

Search this topic... | Search Desktop Intelligence... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Desktop Intelligence  Previous TopicPrint TopicNext Topic
Author Message
Peter N
Forum Member
Forum Member

Joined: 03 Sep 2011

Posts: 1

PostPosted: Sat Sep 03, 2011 2:59 am 
Post subject: Calculation Context - count v countAll, dims v measures

Hi all - I'm relatively inexperienced with BO. With calculation contexts in formulae I can apply them well enough to some aggregates like sum, min, average etc., but am having a tough time trying to get my head around counts.If anyone can help explain the issue described below I'd be very grateful.

I'm using Desktop Intelligence XI, version number seems to be So here's a mockup of the problem using Island Resorts Marketing:
- Use a base query returning Year, Country, City, Revenue. Add a condition where Country='US' just for ease.
- Then drop into a new report a table with Country and Year. Insert a column for a count formula.

Heres where my confusion begins!
If this formula is entered:
You get these results, which seem correct:
Image link

Instead if this formula is used:
You get these results: Image link

Question 1: Why are the results different?
I thought the only difference between count and countall is that count would not double count duplicates or blank rows. Considering in the query raw results there are no duplicates I presumed the two results sets would be the same?

Now to go further I examined the full extended syntax of the count formula, it showed:
=Count(<City>) In (<Country>, <Year>)
Now based on my understanding of contexts, I should have entered this formula to get these results:
=Count(<City> in (<Country>,<Year>,<City>)) In (<Country>, <Year>)
(OK the output context is the same as the block so could be left off)

The key thing is the input context which BO is not showing. I presume as <Country>, <Year> is the default context of the block and that I asked to count <City>, behind the scenes BO is actually adding the input context of : in (<Country>,<Year>,<City>)

Question 2: Is this presumption correct? Is BO actually calculating the formula using this input context but not displaying it?

Question 3: When I try to manually enter an input context using the 'in' operator, it won't let me! I get a syntax error message. Why, why, why?? All the documentation suggests extended syntax should be usable here!
I get this for both the count and contall. i.e. if I tried to enter the formula "=Count(<City> in (<Country>,<Year>,<City>))" I get a syntax error message.
Now if I change the counts to act upon <Revenue> instead of <City>, I can specify the input context. So either of these formulae can be entered successfully without syntax errors and give me the correct results:
=Count(<Revenue>  In (<City>,<Country>,<Year>))

]=CountAll(<Revenue>  In (<City> ,<Country> ,<Year>))

Q4: Why can I specify the input context for a count on a measure but not a dimension?
I understand count on a measure will count all items as opposed to a count on a dimension returning distinct rows. But given this why didn't my CountAll(<City>) work?

Anway if anyone can shed some light on ths for me, it would be greatly appreciated!

[Edit: Removed many blank and unnecessary lines, added code formatting icon_neutral.gif Thanks, Andreas.]
Back to top
Principal Member
Principal Member

Joined: 26 Oct 2005

Posts: 164

PostPosted: Mon Sep 05, 2011 8:35 am 
Post subject: Re: Calculation Context - count v countAll, dims v measures

This is how it works in very simple terms:
If you have a table of just city, count(<city>) will be unique city i.e. 18 in all. If it is countall(<city>) it will be 35, i.e, it counts cities many times over based on every time they occur within each year.
I am still going through your other points...
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Desktop Intelligence  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0286 seconds using 17 queries. (SQL 0.0028 Parse 0.0009 Other 0.0249)
CCBot/2.0 (
Hosted by | Terms of Service
phpBB Customizations by the
Shameless plug for Moments of Light Logo