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

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
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 12.3.0.601. 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:
Code:
=Count(<City>)
You get these results, which seem correct:
Image link

Instead if this formula is used:
Code:
=CountAll(<City>)
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:
Code:
=Count(<City>) In (<Country>, <Year>)
Now based on my understanding of contexts, I should have entered this formula to get these results:
Code:
=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:
Code:
=Count(<Revenue>  In (<City>,<Country>,<Year>))

Code:
]=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
Uvee
Principal Member
Principal Member



Joined: 26 Oct 2005

Posts: 164


flag
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 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0279 seconds using 17 queries. (SQL 0.0026 Parse 0.0008 Other 0.0245)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo