Help with Value based on Max Field

[antoniodneto]

Hi guys,

Could you help me?

I have a table:

COMPANY           NAME                    RATE                  SALE

BANK                    1                                 5                        500

BANK                    2                                 3                       2000

BANK                    3                                 0                        300

On a KPI, I need to show the RATE based on MAX VALUE, is that possible?

Tks!!

[Mauritz_SA]

Hi Antonio

I am not sure exactly what you want to see. Say for example you have the following table which is a slight modification of yours:

COMPANY	NAME	RATE	SALE
BANK	1	5	500
BANK	2	3	2000
BANK	3	4	2000
BANK	4	0	300
BANK	5	4	2000

If you just want to see the Max RATE then you can simply type MAX(RATE). Returns 5.

If you want to see the RATE where another field is the max (for example SALE), then you might have more than one rate returned (if two rows have a SALE value equal to the max sale value) and you might want to concatenate the distinct rates using something like CONCAT(DISTINCT{<SALE = {'$(=MAX(SALE))'}>}RATE,'|'). Returns 3|4. Without distinct it returns 3|4|4.

If you want to see the max rate where another column is equal to the max value in that column (for example the max rate where the SALE value is equal to the max value in the SALE column) then you can use MAX({<SALE = {'$(=MAX(SALE))'}>}RATE). Returns 4.

Hope this helps.

Regards,

Mauritz

[antoniodneto]

Hi @Mauritz_SA 

I think something is going wrong

My expression

CONCAT(DISTINCT{<RISCO_TOTAL = {'$(=MAX(RISCO_TOTAL))'},DATA = {'$(=MAX(DATA))'}>}RADAR,'|')

I Added to set analysis MAX(DATA) so it should me my return also max date field.

It should me return Sep/19 but my KPI is returning NULL.

When I select Sep/19 is working the set analysis, but doesn't work to show RADAR based on MAX RISCO.

RADAR 3 = 527

RADAR 1 = 497

These are the only two values, but KPI returns 1|3.

Can you help me?

[Mauritz_SA]

Hi Antonio

I am not close to my PC at the moment, but I can help you on Monday if you want me to If you are getting a NULL value it is probably because you don't have a row where both the max conditions are satisfied. I am not close to my PC at the moment, but I can help you on Monday if you want me to. You can post a sample of your data with the expected output and I can help with the expression.

Enjoy your weekend.

Regards,

Mauritz

[Channa]

if you looking for max sale for each rate

try 

max(aggr(sum(sale),Rete)

[antoniodneto]

Hi @Channa,

Sorry, I was on vacations and returning today.

No, actually I need the RATE of MAX SALES.

For example, one client has many values of SALES and for each value has a specific RATE, in my KPI I need the RATE of MAX SALE's value adding these informations always on MAX DATE.

Can you help me?

Tks!