
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aggr() and if()
Hi Everyone,
I am trying to build a bar chart with defined dimensions. The dimensions are defined by =valuelist('Top','Rest') , and the measures should be presented as the sum of Invoice Amounts of top 5 performers and sum of Invoice Amounts of the rest.
I tried to define this as the script shown below. For the 'Top' part I am getting a correct value, but the second part is showing empty.
if( valuelist('Top','Rest')='Top',
sum(aggr( IF( aggr ( Rank(sum([Invoice Amount])),[Customer Name])<=5,
sum([Invoice Amount])),[Customer Name])),
sum(aggr( IF( aggr ( Rank(sum([Invoice Amount])),[Customer Name])>5,
sum([Invoice Amount] )),[Customer Name]))
)
Could anyone help me out with how to follow from here on?
Thanks!
Edil
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Try This,
if( valuelist('Top','Rest')='Top',
IF( aggr ( Rank(sum([Invoice Amount])),[Customer Name])<=5,sum([Invoice Amount])),[Customer Name])),
IF( aggr ( Rank(sum([Invoice Amount])),[Customer Name])>5, sum([Invoice Amount] )),[Customer Name])))
pls correct the syntax if i am wrong else could u pls share your application

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Sunny T,
So instead of using a Synthetic Dimension (i.e =valuelist('Top','Rest')), which turned out to be tricky to work together with when using Aggr() function, I chose a simpler way. Which is:
Dimension:
if( aggr ( Rank(sum([Invoice Amount])),[Customer Name])<=5, 'Top', 'Rest');
Measure:
sum([Invoice Amount])
This easily does the thing I need.
I think I just overthought the difficulty of the project.
