1 2 Previous Next 25 Replies Latest reply on Aug 21, 2018 12:07 PM by George Morris

# How to Do a Relative Price Calculation

I'm a newbie. Let's just put that out there.

I want to divide one product value by another product value. Seems easy enough, but I can't figure it out.

In this example I need to divide Choice White Grease - Chicago's daily price by Soy Meal (Hi Pro) - CME Group's daily price for that specific day, across all days. I'm lost as to even begin to think about how to do this.

Can any one assist? • ###### 1. Re: How to Do a Relative Price Calculation

{{Fixed Name, Name (Regions),DATETRUNC('day', Day Applies To):AVG(IF Name (Regions)= "Chicago" THEN Daily Price END)}

/

{{Fixed Name, Name (Regions),DATETRUNC('day', Day Applies To):AVG(IF Name (Regions)= "CME Group" THEN Daily Price END)}

• ###### 2. Re: How to Do a Relative Price Calculation

Thank you Deepak, but I'm getting errors when I try adding that as a calculation. As a newbie I'm likely doing it wrong but I changed the "Day Applies To" to actual dates and that didn't seem to fix it either. Hmmm, sorry for the lack of knowledge on my part.

• ###### 3. Re: How to Do a Relative Price Calculation

Can you provide screenshot of error

• ###### 5. Re: How to Do a Relative Price Calculation

I'm guessing I should have read your solution as two separate calculations?

• ###### 6. Re: How to Do a Relative Price Calculation

This seems to work a bit better, I had to change the format of the calculation but it validated, not sure what it's showing me however. Basically I need to divide the value of Soy Meal by 100, then divide it into each of the regions for Choice White Grease, which will give me a relative %. Choice White Grease is one of 140 other products which will be divided against Soy Meal. I hope that adds clarity. • ###### 7. Re: How to Do a Relative Price Calculation

No George, This Will not Work. The Original Expression I gave you Was Correct. Only Thing I would Modify Is:

{Fixed Name, DATETRUNC('day', Day Applies To):AVG(IF Name (Regions)= "Chicago" THEN Daily Price END)}

/

{Fixed Name, DATETRUNC('day', Day Applies To):AVG(IF Name (Regions)= "CME Group" THEN Daily Price END)}

Try Now

• ###### 8. Re: How to Do a Relative Price Calculation

Hello George, Please Find The Example I Created, Since you are doing analysis of Ratio, So In order to make That Work, you need to keep only Dates in View and take out name and Name(Regions from Rows.

That won't affect you as it will be consolidated View. Here is example from Superstore and attachment.

Thanks

Deepak

If It Helps, Pl Mark IT Helpful and CORRECT to close Thread Calcs:

Numerator:

{FIXED [Segment],[Category],DATETRUNC('day', [Order Date]):AVG( IF Segment="Consumer" and Category ="Furniture" THEN Sales END)}

Denominator:

{FIXED [Segment],[Category],DATETRUNC('day', [Order Date]):AVG( IF Segment="Home Office" and Category ="Technology" THEN Sales END)}

Ratio:

SUM([Numerator])/SUM([Denominator])

So My apologies, I Forgot to mEntion Aggregation as Ratio  Earlier, Now Ratio becomes

SUM({FIXED [Segment],[Category],DATETRUNC('day', [Order Date]):AVG( IF Segment="Consumer" and Category ="Furniture" THEN Sales END)})/

SUM({FIXED [Segment],[Category],DATETRUNC('day', [Order Date]):AVG( IF Segment="Home Office" and Category ="Technology" THEN Sales END)})

• ###### 9. Re: How to Do a Relative Price Calculation

Attachment

• ###### 10. Re: How to Do a Relative Price Calculation Thanks for your help on this. Here is what I have along with what it shows. We're moving along now.. but it's still not right.

I've removed Product Name / Region Name from the Numerator formula as you instructed, I left CME Group in there for the Region on Soy Meal (Hi Pro) so I could divide that value into the other products.

Numerator

SUM({FIXED DATETRUNC('day', [Day Applies To]):AVG([Daily Price])})

Denominator

SUM({FIXED DATETRUNC('day', [Day Applies To]):AVG( IF [Name (Regions)] ="CME Group" THEN [Daily Price] END)})

RelativeToSBM

[Numerator]/[Denominator]

... I'm not sure what I'm missing.

• ###### 11. Re: How to Do a Relative Price Calculation

As I Said, Pl Try This

SUM({Fixed Name, DATETRUNC('day', Day Applies To):AVG(IF Name (Regions)= "Chicago" THEN Daily Price END)})

/

SUM({Fixed Name, DATETRUNC('day', Day Applies To):AVG(IF Name (Regions)= "CME Group" THEN Daily Price END)})

You have to compare Chicago with CME group. What you are doing in your Numerator is that you are  comparing all Data with CME. So not getting  what you are want. Pl use above.,

• ###### 13. Re: How to Do a Relative Price Calculation

That is Because you need placeholders to get that Ratio, Please Follow my attachment earlier. Do The way, I set up view. You are Comparing One Dimensional Value with Other so view has to be like this: Thanks

Deepak

• ###### 14. Re: How to Do a Relative Price Calculation

Ok, that works when I compare 1 product (CWG) against Soy, yet do I have to do that for EVERY product comparing against Soy? Or is there a way to tweak the formula so the row of product / region data is divided by the Denominator on each specific row, creating a % for each product for each day? 1 2 Previous Next