Using Aggregation in a Formulation

Hello Dear All,

I am doing an analyze for a supplier company’s inventory. The data-set is like this in the image below.

What I want to do is to determine the valuable products according to frequency and price rank. So, for this purpose I firstly aggregate the all products with price rank and created new column represent the frequency counted.

And frequency is calculated with this KPI: COUNT_TABLE(“PM_Last.csv_CASES”)

And now I just want to apply a manual RFM algorithm to determine most valuable products. So, I need to use the values of this two column and create a monetary value in another column. For this, I just need to multiply the 2 column, such as the formulation that I tried :slight_smile: COUNT_TABLE(“PM_Last.csv_CASES”) * “PM_Last.csv”.“BuyPriceRank”

But, I receive this error message :

I think, what I want to do is not complex but I couldn’t find a way to use aggregation in a basic formulation to show in another column in the same table.

I would really appreciate if someone could give some advice.

Thank you all and have a happy working days.

Hi Emre,

Sorry for the late reply! So I expect that each Product ID always has the same value for BuyPriceRank right? Did you try simply using the average of the BuyPriceRank?
I created some demo data to test different solutions and this one worked for me:

COUNT_TABLE(“PM_Last.csv_CASES”) * AVG(“PM_Last.csv”.“BuyPriceRank”)

As you are now using the AVG(), you do not mix up aggregations and functions anymore. And if the BuyPriceRank is the same per Product ID, the average results in the same value as if you used the column itself. Although this worked for me, please evaluate whether the formula results in the correct values in your case. If you encounter any issues during the implementation or the formula does not work for you, please get back to us!

Best,
Viana

1 Like

Dear Viana,

Thank you so much for your advice. It works correctly.

Best regards.