1 Reply Latest reply on Oct 2, 2018 5:20 PM by Esther Aller

    Applying discrete colors to a Total function

    Sergio Alonso

      Hi everyone,

       

      I've been looking the past few days for info about how to apply a discrete color, and I've seen very cool ideas on the community on how to solve my problem. Right now, what I'm trying to achieve is this:

       

      General View.png

       

      For each "DESCR", I want my function to make this operation:

       

      Function.PNG

      So, when it goes on "1 bola de helado", I need it to put different colors, and I want a color for when an ingredient cost is over 50% of the total cost for the "DESCR" ("Helado de chocolate" has a cost of 0.34€, so on the total of the "1 bola de helado" cost, which is 0.39€, it's over 50% of the cost, so it should be marked as an "OK".) The "perfect" part is to apply white on my subtotals, as the function applied the "NOT OK" for them (¿?). I'm also having a bit of trouble on this one, because when there is only one subingredient for a recipe, it goes "Perfect" and the cost goes White, so I can't see it

       

      My problem right now is that when I change my calculated field and put a ">0.5" on this part (IFNULL(([Coste de los ingredientes])/TOTAL([Coste de los ingredientes]),0)>1), it goes insane and starts to apply the "OK" to costs that shouldn't be "OK". The fact is, as the function is defined right now, it makes NO sense whatsoever: it's impossible for a subingredient cost to be over the total of the recipe, unless it's the only ingredient for that recipe, in which case it would be labeled as "Perfect".

       

      I would like to think that there aren't any problems on my connection (i'm working on an external database using MySQL), or on the structure I made, as every cost is working as intended. That's why I think the problem has to be on this formula.

       

      I'm sorry, but I can't post the Workbook here, as the info i'm using is from some client.

       

      Any kind of help would be kindly appreciated!

       

      Thanks in advance!

        • 1. Re: Applying discrete colors to a Total function
          Esther Aller

          Hi Sergio,

           

          I understand that you can't share a workbook. With questions like these the answer is usually very specific to the structure of the data, so it is hard to give you an answer or any help without one.

           

          Nothing looks wrong with your calculation. As a next step, can you make a view that has columns for [DISCRETE INGREDENTS], [Coste de los ingredientes], TOTAL([Coste de los ingredientes], and (IFNULL(([Coste de los ingredientes])/TOTAL([Coste de los ingredientes]),0)

           

          If you can take a screenshot of that view and share it with us, we can get more of an idea of where the calculation is going wrong.