Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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')
=Sum({$<[ANUM]={12345','12364','126346','146345'},[BS Date]={"$(=Date(Max([BS Date]),'MM/DD/YYYY'))"}>}[BALANCE])
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]))
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?
first my mistake, match()>0 if anum contains one of the values it gives back the number, otherwise 0
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
can you post small example?
what does ANUM contains?
This is an example of the data
=Sum({$<[ANUM]={12345','12364','126346','146345'},[BS Date]={"$(=Date(Max([BS Date]),'MM/DD/YYYY'))"}>}[BALANCE])
Jannet,
That worked. Thank you.
Jeff
Hi,
you are welcome Jeff.