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.
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
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
Emmanuelle
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.
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
"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
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
Total Wage per job type = wage[SUM: Job type]
6)MAX: round up
MAX: round up= ROUND('MAX/2', 0, UP)
7)MAX: round down
MAX: round down= ROUND('MAX/2', 0, DOWN)
8)SUM: Wage related to MAX round down
SUM: Wage related to MAX round down = Wage related to MAX round down
9)Wage related to MAX round up
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
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
Hi Community, I’m encountering an issue where an export created in Anaplan is missing column headers when retrieved via the API. However, when I manually run the same export from the Actions tab, the downloaded file includes the headers. Has anyone experienced this behavior before? I’d appreciate any insights into the root…
Hi all, I, as an Anaplan workspace administrator, am working together with a Fabric team to export data from Anaplan to Fabric via Anaplan API and Postman. We have a problem where all Anaplan exports are visible in Postman but when trying to connect to SOME of them, we get message "404, Not found". Below screenshot showing…
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…
