Set Analysis: get a pivot table

[maverick1984_c]

Hi, I have the following table 

qualify *;

B:
LOAD * INLINE [
master_process_name,step_process_name, nb_run, step_exec_time
a,step1,1,10
a,step1,2,20
a,step2,1,30
a,step2,2,40
a,step2,3,5
];

I need to create a pivot table with 2 dimensions : "master_process_name" and "step_process_name".

In the pivot table, I need to create an expression that shows me the sum of execution time of all and only the process steps that have the maximum nb_run value inside the same step,
filtering out the all the other process steps.

The attached picture shows the goal I am trying to get. Which set analysis expression (or other solution) should I use?

Thank you , Best Regards

[bharathadde]

Try this on Pivot Table

Note : Please check box "Always Fully Expanded"  option on presentation tab for sure

Dimensions

master_process_name

step_process_name

nb_run

Expression

=if(nb_run=max(total <step_process_name> nb_run),Sum(step_exec_time))

[joseph_morales · Accepted solution]

Hi @maverick1984_c ,

Try with this in the expression:

sum(
aggr(
    if(nb_run=max(total<step_process_name>nb_run),
		Sum(step_exec_time))
,step_process_name,nb_run)
)

[maverick1984_c]

Hi Bharathadde , your solution is almost close to my goal, but it doesn't let to expand / close the pivot. Thank you anyway for the suggestion .

[maverick1984_c]

Thank you Joseph Morales, this is exactly what I was searching for.  TOP !!!!  Best Regards