9 Replies Latest reply: Apr 22, 2015 9:49 AM by Jannet San

# Sum a measure using specific dimensions

I'm attempting to sum a value based on a list of specific dimension values.

The expression I am currently using yields results for all the dimensions as is as follows:

=Sum({\$<[BS Date]={"\$(=Date(Max([BS Date]),'MM/DD/YYYY'))"}>}[BALANCE])

I want to include a list of Account Numbers to limit the aggregated [BALANCE] for just those specific numbers.  Something similar to what you would do in SQL (e.g. Where ANUM in ('12345','12364','126346','146345')

• ###### Re: Sum a measure using specific dimensions

you could expand the set Analysis part with anum={'12345','12364','126346','146345'}

or you can use if statement

if (match(anum,'12345','12364','126346','146345')=1,Sum({\$<[BS Date]={"\$(=Date(Max([BS Date]),'MM/DD/YYYY'))"}>}[BALANCE]))

• ###### Re: Sum a measure using specific dimensions

Tried using the following:

if(match("ANUM",'10502','10502','10505','10507','10510','10511','10513','10520','10521','10523')=1,

Sum({\$<[BS Date]={"\$(=Date(Max([BS Date]),'MM/DD/YYYY'))"}>}[BALANCE]))

No success, do you see anything wrong with my syntax?

• ###### Re: Sum a measure using specific dimensions

first my mistake, match()>0 if anum contains one of the values it gives back the number, otherwise 0

• ###### Re: Sum a measure using specific dimensions

I have the following:

if(match([ANUM],'10502','10502','10505','10507','10510','10511','10513','10520','10521','10523')>0,

Sum({\$<[BS Date]={"\$(=Date(Max([BS Date]),'MM/DD/YYYY'))"}>}[BALANCE]))

• ###### Re: Sum a measure using specific dimensions

can you post small example?

what does ANUM contains?

• ###### Re: Sum a measure using specific dimensions

This is an example of the data

• ###### Re: Sum a measure using specific dimensions

=Sum({\$<[ANUM]={12345','12364','126346','146345'},[BS Date]={"\$(=Date(Max([BS Date]),'MM/DD/YYYY'))"}>}[BALANCE])

• ###### Re: Sum a measure using specific dimensions

Jannet,

That worked.  Thank you.

Jeff

• ###### Re: Sum a measure using specific dimensions

Hi,

you are welcome  Jeff.