topic Re: Sum a measure using specific dimensions in QlikView

Sum a measure using specific dimensions

Tue, 21 Apr 2015 20:49:06 GMT

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

Anonymous — Tue, 21 Apr 2015 20:52:02 GMT

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

Tue, 21 Apr 2015 20:58:38 GMT

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

Anonymous — Tue, 21 Apr 2015 21:09:37 GMT

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

Tue, 21 Apr 2015 21:17:33 GMT

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

Re: Sum a measure using specific dimensions

Anonymous — Tue, 21 Apr 2015 21:31:48 GMT

can you post small example?

what does ANUM contains?

Re: Sum a measure using specific dimensions

Tue, 21 Apr 2015 21:59:07 GMT

This is an example of the data

Re: Sum a measure using specific dimensions

Anonymous — Tue, 21 Apr 2015 22:57:36 GMT

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

Re: Sum a measure using specific dimensions

Wed, 22 Apr 2015 13:41:12 GMT

Jannet,

That worked. Thank you.

Jeff

Re: Sum a measure using specific dimensions

Anonymous — Wed, 22 Apr 2015 13:49:41 GMT

Hi,

you are welcome Jeff.