Evaluate Values within in a Hierarchy

Hello,

Does anyone know of a quick formula combination that evaluates the line item values for list members that share a parent?

In my example provided the puppies have the same parent. They each have different "fees" which I'd like to evaluate in the variance column. How much more was collected in fees for Snoopy than Gizmo?

Dog_Adoptions_69173.png

Accepted answers

MaartenGijbels

Hi Michael,

I added some screenshots how I'd do this. But it would have to be interpreted based on your wanted outcome.

For example: I created the variance to the maximum fee obtained by changing the summary settings. => if you would like to do this you probably want to create a separate line item in order to keep the sum.

I also added the "Mother" as a separate line item, although in a real model it would be best practice to do this via a system module.

But the core mechanics would remain the same, so just finetune as as required 😉

All the best,

Maarten.

2020-05-05_08_29_18-Window_69180.png

2020-05-05_08_29_36-Window_69180.png

All comments

jasonblinn

@michael.chefer

What are you trying to calculate the variance to? The Highest Ranked, the average, the list member above it, etc?

What would the variance state in this example for each Snoopy, Gizmo and Chewy?

MaartenGijbels

Hi Michael,

I added some screenshots how I'd do this. But it would have to be interpreted based on your wanted outcome.

I also added the "Mother" as a separate line item, although in a real model it would be best practice to do this via a system module.

But the core mechanics would remain the same, so just finetune as as required 😉

All the best,

Maarten.

2020-05-05_08_29_18-Window_69180.png

2020-05-05_08_29_36-Window_69180.png

nathan_rudman

I'm not sure what you mean by evaluate

you can do a quick selective variance, with a LOOKUP:

If you want to compare each member against ALL members you're going to have to create parallel list.

Misbah

@michael.chefer

This is the typical use case of Previous and Next functions for non time dimensions. There is an idea shared already which will extend these functions to lists as well - let's see if we get this near soon.

On your query I think @nathan_rudman is right. You need to map it and then use Lookups based on the mapping. That's the only way you can get immediate next or prior values based on the mapping

SandeepB

@michael.chefer,

if I understand your question, You need the ability to compare the price of each puppy that shares the common parent.

I can try something like below, not sure whether it would help or not.

Here is my output screen,

variance grid is filtered based on the Parent selection and To compare is based on dependent drop down to the parent.

You can select the only puppies which share the common parent to check the variance.

Thanks,

Sandeep

michael.chefer

@jasonblinn

The comparison would be against the puppy ranked above it.

So coming up with a variance between 1 and 2, and 2 and 3.

Quick Links

Can you help?

Unanswered questions

OData Rest API for
Hi, Does anyone know how to get an outbound OData Rest API, (OAuth2) to establish a connection between SAP and Anaplan. Thanks Khaled
Looking for Anaplan End-Users to take a short survey!
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.…
CERTIFICATION BADGES - ANNOUNCEMENT
Anaplan Champions! The Community team just posted this announcement that certification badges may not be showing up on your profile probably until next year. Rest assured, you will get credit once you complete and pass the exams. https://community.anaplan.com/t5/Blog/Badges-Back-Soon/ba-p/123385