# QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

## 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')

1 Solution

Accepted Solutions
Valued Contributor

## Re: Sum a measure using specific dimensions

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

9 Replies
Honored Contributor

## 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]))

Not applicable

## 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?

Honored Contributor

## 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

Not applicable

## 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]))

No results

Honored Contributor

## Re: Sum a measure using specific dimensions

can you post small example?

what does ANUM contains?

Not applicable

## Re: Sum a measure using specific dimensions

This is an example of the data

Valued Contributor

## Re: Sum a measure using specific dimensions

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

Not applicable

## Re: Sum a measure using specific dimensions

Jannet,

That worked.  Thank you.

Jeff

Valued Contributor

## Re: Sum a measure using specific dimensions

Hi,

you are welcome  Jeff.