Evaluate Values within in a Hierarchy

michael.chefer

 

 

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?

 

 

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.

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?

 

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.