Creating a Difference/Comparison Metric

Question & Answer
, ,
1

Hello,

I have a data set with a ton of records. Mainly 4 important parts
Period - When something occurred. Example Period 1. This also has a date attached to it if need be formatted as yyyy-MM-dd
Rate - This is a currency dollar value. Example $1,200.00
Product - This is a string name. Example: Phone
Management Company - This is just a string name of a company. Example Costco

I can’t quite seem to figure out how to set this up. But I’m looking for a metric that would show the difference between what one management company is charging opposed to the rest.

For example, if Costco is charging $1,200 for a phone and 3 competitors are charging, 1,000/1,500/2,000. I’d want it to show that the market average difference of those three companies over Costco. ideally it would be two calculated fields of the average of Costco for the most recent period and then the average of the market for the most recent period. The most recent period changing each week as new data is entered. My big hang up is how do I make sure it’s only comparing the most recent data to each other rather than the entire table of values.

2

Hello @ScottP !

I think the first place to start is creating all the calculated fields. For finding the average of the non-selected companies you could create a parameter control for the selected management company. Then create a calculated field base on that control to find the avg market value:

ifelse({ManagementCo} <> ${SelectedManagementCo}, avgOver(sum{Rate}, [{Product}], PRE_AGG), 0)

For the average cost based on period, you could create a multi select parameter for the period’s you want to measure by (Year, Quarter, Month, etc…) and then apply that to an avgOver for the selected management co. in the first control filter:

avgOver(sum{Rate}, [Product, ${Period}, ${SelectedMangCo}], PRE_AGG)

I’m not sure if you would get a mismatch aggregation error here but you could then do a calculated field that does Calculation 1 - Calculation 2.

I wasn’t able to test these but I believe this is heading towards your expected outcome. Let me know if this helps!

3

Thank you for the answer Duncan,

I can’t seem to get past this portion. As it is saying my syntax is wrong somewhere. Does anything look glaringly incorrect or should there be something in the data that may throw a wrench in it?

ifelse({management} <> ${CalcManagement}, avgOver(sum({advertised_rate}, [{floorplan_beds}], PRE_AGG), 0)

4

Hey @ScottP,

It might be a missing parentheses at the end of this portion of your avgOver: sum({advertised_rate} > sum({advertised_rate})

Let me know if that helps!

5

ifelse({management} <> ${CalcManagement}, avgOver(sum({advertised_rate}), [{floorplan_beds}], PRE_AGG), 0)

No that still is giving me some sort of syntax error. Wouldn’t the parenthesis need to be open to include the bracketed value?
The *Over formulas are new to me.

6

Hey @ScottP !

No problem, LAC functions are a pretty interesting in how they work. I would check out the documentation here for a detailed breakdown on sumOver:

Looking at this now, might work better to nest the ifelse in the LAC function. Something like:

avgOver(ifesle({management} <> ${CalcManagement}, {advertised_rate}, 0), [{floorplan_beds}], PRE_AGG)

7

Hi, @ScottP. We hope @duncan’s solution worked for you. Let us know if this is resolved. And if it is, please help the community by marking this answer as a “Solution" (check box under the reply).