Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Anonymous
Not applicable
Author

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

View solution in original post

9 Replies
Anonymous
Not applicable
Author

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
Author

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?

Anonymous
Not applicable
Author

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

Not applicable
Author

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

Anonymous
Not applicable
Author

can you post small example?

what does ANUM contains?

Not applicable
Author

This is an example of the data

Balance Sheet ANUMs.png

Anonymous
Not applicable
Author

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

Not applicable
Author

Jannet,

That worked.  Thank you.

Jeff

Anonymous
Not applicable
Author

Hi,

you are welcome  Jeff.