Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSAS 2005 using count of dimension members as measure Expand / Collapse
Author
Message
Posted Friday, August 22, 2008 2:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 05, 2013 4:21 AM
Points: 74, Visits: 90
Hi, I have a dimension for Accounts. These accounts have a start date
for when they are first active and an end date for when they become
classed as "Former Accounts". As "Former Accounts" they are still to
be included in some reports so they are not inactive.

At the moment I calculate the balance on each account using
PreviousPeriod. To count the number of accounts with debit balances I
use;


Count(Filter(([Accounts].[Accounts].Members,[Measures].[Account
Balances C Fwd]>0)


To count the number of accounts with credit balances I use;


Count(Filter(([Accounts].[Accounts].Members,[Measures].[Account
Balances C Fwd]<0)


To count the number of accounts with zero balances I use;


Count(Filter(([Accounts].[Accounts].Members,[Measures].[Account
Balances C Fwd]=0)


And to count the total number of accounts I simply add the previous
measures together.


The issue is that the same number of Total Number of Accounts is
returned each period even though I know new accounts are added each
period. I think what might be happening is that in instances of
accounts where no transactions exist prior to the date selected, and
hence no balance has been calculated, the NULL balance is been treated
as zero.


Does this seem plausible? If I am correct how can I go about only
counting those accounts where there is a balance?


Thanks in advance for any suggestions or ideas.


Bye for now


Duncan



All the best,

Duncan

Post #557117
Posted Wednesday, August 27, 2008 8:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 22, 2012 9:58 AM
Points: 7, Visits: 44
The question that comes to mind is how is the context being set for the evaluation of the expressions. The balance will be evaluated in the context of the current query, but the query will determine if the timeframe is something other than All. Can you post the entire query that is being used?

JW
Post #559673
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse