Get the Max value and use it in KPI

Question & Answer
,
1

Hi I just want to know how to display the max and not the total in a KPI? Below is my table and it shows the max = 35.19 and total is -9.06.

I use a calculation like this but it shows the total
maxOver(

(sum({Mins today’s time}) - sum({Mins yesterday time })) / sum({Mins yesterday time }))

image
image468×569 31.9 KB

2

Hi @danielreyes3rd,
In your field well, what’s your aggregation set at?

image
image630×506 34.7 KB

2 Likes
3

Hi Bret thanks for the response however my calculation wont show those options.

image
image266×150 8.14 KB

1 Like
4

Hi @danielreyes3rd,
So I assume that ‘test’ is a calculated field then? If that’s the case, could you share what that syntax is?

2 Likes
5

here you go , thanks

(
sum(
ifelse(
truncDate(‘DD’, {created_ts}) = truncDate(‘DD’, ${EndDate}),
dateDiff({process_start_ts}, {process_end_ts}, ‘SS’) / 60,
NULL))

sum(
ifelse(
truncDate(‘DD’, {created_ts}) = addDateTime(-1, ‘DD’, truncDate(‘DD’, ${EndDate})),
dateDiff({process_start_ts}, {process_end_ts}, ‘SS’) / 60,
NULL))
)
/
sum(
ifelse(
truncDate(‘DD’, {created_ts}) = addDateTime(-1, ‘DD’, truncDate(‘DD’, ${EndDate})),
dateDiff({process_start_ts}, {process_end_ts}, ‘SS’) / 60,
NULL))

6

Hi @danielreyes3rd,
Since your calculated field is built to do a specific type of aggregation, you aren’t able to change the aggregation type to something else.
I would suggest trying to create another calc. field wrapping that in a maxOver and using that for your KPI.

Let me know if that works for your case or if you have any additional questions.

7

Hi Bret I think the maxOver calculation wont work in a KPI?

8

Hello @danielreyes3rd, sorry I missed your response!
The issue with the calculation you are running is that you are expecting it to partition the same way that it is within the pivot table. The table contains the client_region_name field, so that is where each individual sum is occurring. That is completely ignored as soon as it is used within the KPI. You will need the sum functions to occur at the region level, then have the maxOver check across each region sum. It will need to look more like this:

maxOver((sumOver(ifelse(truncDate('DD', {created_ts}) = truncDate('DD', ${EndDate}), dateDiff({process_start_ts}, {process_end_ts}, 'SS')/60, NULL), [{client_region_name}], PRE_AGG) - sumOver(ifelse(truncDate(‘DD’, {created_ts}) = addDateTime(-1, ‘DD’, truncDate(‘DD’, ${EndDate})), dateDiff({process_start_ts}, {process_end_ts}, ‘SS’) / 60, NULL), [{client_region_name}], PRE_AGG)) / sumOver(ifelse(truncDate(‘DD’, {created_ts}) = addDateTime(-1, ‘DD’, truncDate(‘DD’, ${EndDate})), dateDiff({process_start_ts}, {process_end_ts}, ‘SS’) / 60, NULL), [{client_region_name}], PRE_AGG), [], PRE_AGG)

Here, you will notice that the sumOver functions contain the client_region_name field within the partition, but the maxOver will have an empty partition field. This should allow it to get the max value across all of the region. Let me know if this helps!

9

This is awesome Thanks Brett

2 Likes