topic Re: Color in straight table based on condition in QlikView

Color in straight table based on condition

Thu, 17 Mar 2016 10:34:15 GMT

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

Re: Color in straight table based on condition

Gysbert_Wassenaar — Thu, 17 Mar 2016 15:55:12 GMT

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?

Re: Color in straight table based on condition

amayuresh — Fri, 18 Mar 2016 07:00:00 GMT

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);

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

Re: Color in straight table based on condition

Kushal_Chawda — Fri, 18 Mar 2016 07:13:34 GMT

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 %.

Re: Color in straight table based on condition

Fri, 18 Mar 2016 07:23:07 GMT

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

Re: Color in straight table based on condition

Fri, 18 Mar 2016 07:25:48 GMT

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

Re: Color in straight table based on condition

Fri, 18 Mar 2016 07:27:55 GMT

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