Calculate median value

Occasional Contributor

Calculate median value

Hello,

the MEDIAN Anaplan function doesn't exist ("No native Anaplan function: can be built using calculation").

I have all the wages of my company and I need to calculate the median wage per job type. How can I do ?

Thank you for your help.

Tags (1)
Message 1 of 6
5 REPLIES 5
Community Boss

Re: Calculate median value

Hi,

It can be acheived using RANK & MAX formulae.

1) Using RANK, give the rankings to your wages lineitem.

2) Select MAX rank from the previous line

3) Then max value calculated in previous line divide by "2" (Use ROUND function, if value is odd)

4) In line 3, you get MEDIAN RANK. Wages corresponding to your median rank, is your median wage.

 

Hope it helps

 

Regards,

Pavan

Message 2 of 6
Occasional Contributor

Re: Calculate median value

Hello,

 

Do you mean ?:

 

Line item 1:   Wage [SUM: Job type]

Line item 2:   RANK(Line item 1, ASCENDING)

Line item 3:   MAX(Line item 1, Line item 2) / 2

 

Regards,

 

Emmanuelle

Message 3 of 6
Community Boss

Re: Calculate median value

Emmanuelle,

 

For Lineitem 2: Set the summary as "Max" in blueprint view

For Lineitem 3: Formula = Lineitem 2 [Lookup: Parent ] 

For Lineitem 4: Formula = ROUND(Lineitem 3 / Two)

Lineitem 4 will give us the median Rank. Then, wages corresponding to Line 4 will be our median wage.

 

Hope this helps.

 

Regards,

Pavan 

 

 

Message 4 of 6
Highlighted
Community Boss

Re: Calculate median value

Hi, I typically accomplish these types of calcs by creating a 2nd module to aggregate (in this case) Total Wages by Category, and a Count of Items ... the calc the average and consume it wherever I need it. In this case, the aggregation module would have Category as a dimension, so a SUM statement can be used.
Message 5 of 6
Occasional Contributor

Re: Calculate median value

Dear both,

 

thank you for your help.

 

I found out the solution (I've checked with Excel), which is a little bit more tricky ;-) 

It would be nice if Anaplan edit a MEDIAN formula ;-) 

 

Here are the line item formula:

 

lists:

Job type: TEXT (example: accountant, Sales...)

Employee: TEXT (employee name)

 

1)MEDIAN

 

"Applies to ": Job type

 

MEDIAN = IF 'MAX/2' * 2 = 1 THEN Wage ELSE IF 'MAX/2' * 2 = 2 THEN Median intermediate ELSE Median intermediate

 

2) MAX/2

 

"Applies to ": Job type

"Summary": MAX

 

MAX/2 = Rank per job type[MAX: Job type] / 2

 

3)Rank per job type

 

"Applies to ": Job type, Employees

 

Rank per job type = RANK(Wage, DESCENDING, MAXIMUM, Wage > 0, ITEM(Job type))

 

4)Median intermediate

 

"Applies to ": Job type, Employees

"Summary": MAX

 

Median intermediate = IF 'MAX/2' * 2 = 1 THEN Wage ELSE IF 'MAX/2' * 2 = 2 THEN Total Wage per job type / 2 ELSE IF 'MAX: round up' = 'MAX: round down' THEN IF 'MAX/2'[LOOKUP: Job type] = Rank per job type[LOOKUP: Job type] THEN 'SUM: Wage related to MAX round down' / 2 ELSE 0 ELSE Wage related to MAX round up

 

5) Total Wage per job type

 

"Applies to ": Job type

 

Total Wage per job type = wage[SUM: Job type] 

 

6)MAX: round up

 

"Applies to ": Job type

"Summary": MAX

 

MAX: round up= ROUND('MAX/2', 0, UP)

 

7)MAX: round down

 

"Applies to ": Job type

 

MAX: round down= ROUND('MAX/2', 0, DOWN)

 

8)SUM: Wage related to MAX round down

 

"Applies to ": Job type, Employees

 

SUM: Wage related to MAX round down = Wage related to MAX round down

 

9)Wage related to MAX round up

 

"Applies to ": Job type

 

Wage related to MAX round up = IF 'MAX: round up' = Rank per job type THEN Wage ELSE 0

 

10)Wage related to MAX round down

 

"Applies to ": Job type, Employees

 

Wage related to MAX round down = IF 'MAX: round down' = Rank per job type OR 'MAX: round down' + 1 = Rank per job type THEN Wage ELSE 0

 

 

Emmanuelle

 

 

 

 

Message 6 of 6