Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

The way to achieve your own success is the willingness to help somebody else. Go for it!

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- App Development
- :
- Client with third largest sales

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

2016-09-14
05:02 PM

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

Client with third largest sales

Hi,

I'm trying to create a KPI for each one of the top3 clients by margin. I've so far managed to create a bar chart with the clients with the top 3 margins. But the (slightly) same code won't work for a KPI.

Here's the (working) code for the measure in the bar chart:

*if(rank((sum(Valor) - sum([Custo]*Horas))/sum(Valor))<=3,(sum(Valor) - sum([Custo]*Horas))/sum(Valor))*

And here's the one I've been trying to make work on a KPI:

*if(rank((sum(Valor) - sum([Custo]*Horas))/sum(Valor))=3,(sum(Valor) - sum([Custo]*Horas))/sum(Valor))*

The problem is the KPI is always "-" no matter what value or expression I put in the code above. I assume there's a misunderstanding in how the aggr function works on my part, but couldn't tell where the error is coming from. Can someone help me?

Thanks in advance.

- Tags:
- aggr help
- kpi object

602 Views

1 Solution

Accepted Solutions

sunny_talwar

MVP

2016-09-14
07:42 PM

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

Can you try this:

**Max(Aggr((Sum(Valor) - Sum([Custo]*Horas))/Sum(Valor), Client), 3)**

and may be this to get the name of the 3rd larget client

**FirstSortedValue(Client, -Aggr(****(Sum(Valor) - Sum([Custo]*Horas))/Sum(Valor), Client), 3)**

9 Replies

swuehl

MVP

2016-09-14
05:30 PM

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

I can't spot the aggr() function in your code, have you posted the correct code snippet?

485 Views

swuehl

MVP

2016-09-14
05:31 PM

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

Try maybe

Sum( Aggr(

*if(rank((sum(Valor) - sum([Custo]*Horas))/sum(Valor))<=3,(sum(Valor) - sum([Custo]*Horas))/sum(Valor))*

*, ClientField*

*))*

sunny_talwar

MVP

2016-09-14
07:42 PM

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

Can you try this:

**Max(Aggr((Sum(Valor) - Sum([Custo]*Horas))/Sum(Valor), Client), 3)**

and may be this to get the name of the 3rd larget client

**FirstSortedValue(Client, -Aggr(****(Sum(Valor) - Sum([Custo]*Horas))/Sum(Valor), Client), 3)**

Not applicable

2016-09-15
10:36 AM

Author

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

Oh, I'm sorry. I was testing two or three formulas and ended up posting the wrong ones.

485 Views

Not applicable

2016-09-15
10:39 AM

Author

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

Both formulas worked fine. Thank you a lot! I didn't knew I could pass another parameter to the max function. Can you please explain the use of a minus sign in the aggr function? From what I've tested, removing the sign inverts the "temporary table" create by the aggr function. Is this correct?

485 Views

sunny_talwar

MVP

2016-09-15
02:19 PM

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

Yes, you are right. negative sign in front of Aggr() sort the table in descending order picking the third largest client. If you remove the negative sign, the FirstSortedValue will pick the 3rd smallest client

Not applicable

2016-09-15
05:39 PM

Author

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

I'm also trying to compare this margin over time using an line chart, but the value simply doesn't displays. From what I understand including the "Date.month" in dimensions and this expression in measure would accomplish that, but it's not working.

For testing I've set up a filter based up on month and a KPI displaying the third largest margin. It changes as I filter the months, so it's working alright. But the graphic besides the kpi is always blank.

485 Views

swuehl

MVP

2016-09-15
05:56 PM

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

Have you added your time dimension field used in the charts also to the Aggr() function dimension list?

Not applicable

2016-09-16
10:57 AM

Author

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

Now I have and it worked fine. Thanks! I'm really impressed by such a colaborative community

485 Views