Color in straight table based on condition

[#1]

Hello everyone,

I am currently struggling with a coloring problem of mine. I have the following straight table:

Now I want to have a Background color for each row based on specific thresholds. Each Payment Method has its own three thresholds. For this I have a a table with those data stored.

So for each row in the first image, I want to check the KPI_THRESHOLD_Values where the KPI_NAME is the same as the Payment_Method and get the color from the row where this threshold_value is next bigger value compared to the error rate.

At the moment I am completly unsure on how to achieve this. I hope you can help me.

Regards,

Claus

[Gysbert_Wassenaar]

I don't see any relation between the payment method and the thresholds. How are these linked? Did you forget to rename fields so the tables are associated correctly?

[amayuresh · Accepted solution]

Please is it answer your question.

Output:

If yes, then below is the script and steps

Script:

Data:

LOAD Paymethod,

     Qty,

     Value

FROM

(ooxml, embedded labels, table is Sheet2);

t:

LOAD Paymethod,

     Color,

     Threshold

FROM

(ooxml, embedded labels, table is Sheet3);

Straight Table with

Dimensions: Paymethod, Color Threshold

Expression: = Sum(Qty) and =Sum(Value)

Select Expression > Click on '+' sign > Click Background Color >

=if(sum(Value)>=aggr(Max(Threshold),Paymethod),RGB(51,255,51),

if(sum(Value)>aggr(Min(Threshold,2),Paymethod)and sum(Value)<aggr(Max(Threshold),Paymethod),RGB(255,255,51),

if(sum(Value)<=aggr(Min(Threshold,2),Paymethod),RGB(255,51,51))))

in Definition

[Kushal_Chawda]

First, rename KPI_NAME to PAYMENT_METHOD in script so that your payment info and threshold info are linked.

How would you compare the threshold_value with Error rate? as threshold_value is number and Error rate is on %.

[#5]

Hi and thanks for your answer.

Indeed I had no connection between the two tables because I was not sure on what would be the best datamodel. I came up with a solution I do not really love but works for me. I extended the first table by three columns "GreenThreshold", "YellowThreshold" and "RedThreshold" and with this I was able to achieve at least my goal for this specific case. Nevertheless I would prefer a solution where I can have a any amount of thresholds for one payment method.

Regards

Claus

[#6]

Hi and thanks for your answer.

Your solution works fine for my case. Nevertheless I would prefer some kind of generic solution in order to be able to have any amount of thresholds for any payment method. But for now this works fine.

Regards,

Claus

[#7]

Hi and thanks for your answer.

Indeed I had no connection between the two tables because I was not sure on what would be the best datamodel. The comparison is no problem because both values stand for percentage, I just have to multiply ErrorRate by 100 or divide threshold_value by 100. Now I have a solution that works for me. Thank you nevertheless.

Regards,

Claus