Table with max, Min and Avg Value

[peisenrot]

hello,

my customer would like to have a table like:

Sites	employees	Engr Employee	others
Frankfurt	25	11	14
Mainz	30	15	15
Siegen	45	20	25
min	25	11	14
max	45	20	25
Avg	38,33	17,00	21,33

I know how to do min or max or avg. but is there a way to make a table with all three calculations?

best regards

Pia

[Rohan · Accepted solution]

Hi Pia,

Try This, load this inline table in the load script :

Dim  :

Load * inline [

Dummy

1

2

3

4]; 

& use the following expressions, in the front end :

Dimensions : Sites - pick(Dummy,Sites,'min','max','avg');

Measure 1 : Employees - pick ( Dummy, Count(distinct EMPID), min(aggr(Count(distinct EmpID),Sites)),max(aggr(Count(distinct EmpID),Sites)),avg(aggr(Count(distinct EmpID),Sites)));

Measure 2 : ENGR Employees - pick ( Dummy, Count(distinct EngrEMPID), min(aggr(Count(distinct EngrEMPID),Sites)),max(aggr(Count(distinct EngrEMPID),Sites)),avg(aggr(Count(distinct EngrEMPID),Sites)));

I have assumed Sites to be the the dimension that you have used, & count of employees as the mesaure shown in your table.

Try this out & revert if you face any issues. The concept i am using here is something called as a Dummy Dimension.

Regards,

Rohan.

[Anil_Babu_Samineni]

I agree @Rohan If that is in UI, But in expression you  don't need Aggr(), instead you can simplify an example Min(employees).

[Rohan]

Hi @Anil_Babu_Samineni ,

I have added that Aggr because that min, max & avg seems to be calculated at Site level.

Regards,

Rohan.