Now, at the moment, I can't. But I'm going to give you an example here.
Suppose we have 3 categories, like pizza, meat and vegetables. For the category 'Pizza', we have the products 'pizza with ham' that costs €5.00, 'pizza with potatoes' that costs €6.00 exc exc.
In other words, we have this situation:
FAMILY PRODUCT PRICE
pizza pizza with ham €5.00
pizza pizza with potatoes €6.00
pizza .................... ............
meat sausages €4.00
meat .......... ..........
The most sold pizza is 'pizza with ham'. Now, I want to find the price of the pizza that contains the word 'potatoes' and compare it with the price of 'pizza with ham' (that's, how said before, the pizza with the highest revenue), indipendent of the position of the pizza with potatoes. The comparison has to be done as the price of the pizza with potatoes divided by the price of the first pizza (ham).
The result has to be a table that contains, for each family: the product with the research I want (in this example: 'potatoes'), the first product with highest revenue (pizza with ham) and the price variation between these products.
Looks like you can create a field in the script that contains the price of the product with the largest revenue:
LOAD FAMILY, FirstSortedValue(PRICE, -Revenue) as PriceOfMaxRevenueProduct
GROUP BY FAMILY;
You can then use an expression to calculate the ratio: =PriceOfMaxRevenueProduct/PRICE
Try like this
LOAD * INLINE [
FAMILY, PRODUCT, PRICE, Revenue
Pizza, pizza with ham, 5, 2000
Pizza, pizza with sausages, 6, 800
Pizza, pizza with potatoes, 6, 1500
Pizza, , 5, 1800
Meat, chicken, 10, 500
Meat, sausages and potatoes, 8, 300
Load FAMILY, FirstSortedValue(PRICE, - Revenue) as MaxRevenuePricePerProd Resident ProdTemp
Group by FAMILY;
Load *, if(WildMatch(PRODUCT, '*potatoes*'), PRICE / MaxRevenuePricePerProd) as PriceComparision Resident ProdTemp;
DROP Table ProdTemp;