Calculated Field to create market index price

I want to create a calculated field to track market index for company analysis. We want to track the index per month by dividing each websites price by our website price for a respective month.

The image below illustrates the layout of the table. So, for example, in Jan 2021 our company has price of $1,000,000 and name_1 has price of $1,010,417.84. So the index is $1,010,417.84/$1,000,000*100.

How can I create the new column with the calculated field, called ‘IndexValue’?

Thank you!

Hi RossDickinson97,

Do you have your website’s price per month in your dataset? We would need this field to calculate that value.

Once you have that field it’d be a calculation of website price / your_price.

Hi @Peter,
I think this calculation should work for you. It basically sums op the price over each record after it has been aggregated (POST_AGG_FILTER).

(sum({price})/
sumOver(
sum(ifelse(website_name='my_name',{price},0))
,[{auction_enddate}]
,POST_AGG_FILTER
))*100
2 Likes

Thank you @bergqdou! This works.