Qlik Community

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
maverjannet
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
hrlinder
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?

hrlinder
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

hrlinder
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

Balance Sheet ANUMs.png

maverjannet
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

maverjannet
Valued Contributor

Re: Sum a measure using specific dimensions

Hi,

you are welcome  Jeff.

Community Browser