This is really not an easy task even in softwares older than palo. The core of your problem is that you would you like to put the rank number into cells as value (if I understand well.)
Some years before I had the same problem in TM1 where I could solve it using dinamic subsets and processes. I tried to check from the ETL manual if the same thing is possible in Palo, but as I find it, exactly the same thing is impossible and maybe working with the ETL server is not the nicest thing anyway.
However, maybe a solution can be engineered. I have not tried it now, check it out! Here are the steps:
1: Create a subset in Excel in the Products dimension sorting the elements by the values that are the base of the ordering. (You have Palo.Sort, it should do that for you.)
2. So you have the sorted product names in one column of an Excel sheet. Now loop through the list and build a technical dimension within Palo. If the dimension already exists, you can destroy it before the building, it has really technical purposes.
3. The elements are ordered in the dimension, right? The product with the largest value is the first dimension element, the product with the second largest vaélue is the second and so on. Now, using the Palo function PALO.EINDEX you can retrieve the order number for each product. Note that the rule necessary for this need to be written just once. You do not have to change it later and the rule is independent of the number of the products.
4. This way you can have the ordering number besides the basic values, like you wished: Product |Value | Rank. One more step: do not forget, that tha Rank will change each time you do the above procedure, because the Palo.Eindex will retrieve the current element index. Maybe you would like to save the ordering somewhere, inside or outside Palo - but that is another story.
-------
One warning: I have a post in an another topic where I indicated that the sorting may be buggy when based on cells with marker and works only for cells without markers. This far no one has validated or denied this. Maybe only sorting based on unmarkered cells is secure.
