How coluld I achieve this average price calculation in Yellowfin?
I would like to calculate an average price, based on a dataset similar as this one:
With a calculated field, I get the price (€/Kg) for each day with no problem. What I want to do next, is to accumulate the quantity (Kg) and the Amount(€) for each day. Then, with this accumulated data, I want to get an average price. In excel, it would be:
where Acumm. €/Kg = Acumm. € / Acumm. Kg
I have tried to do this in Yellowfin, with no luck.
1.- With advanced function (Acummulated total), I get the Acumm. Kg and Acumm. €, but I cannot add a second advanced function to achieve the division, nor use this fields for a calculated field.
2.- I have tried to use a subquery with the advanced functions for Acumm. Kg and Acumm. €, and use this fields in the main query to create a calculated field. This does not work, the value for the calculated field does not use the advanced function result, maybe it is a bug?
What would be the best way to achieve this?