Setting the Colors as per sum of status

[dmohanty]

Hi All,

I have a challenge in achieving this. Suppose I have a table with data like this (PLEASE IGNORE HEADER 5):

EmpID	KPIs	Status	Month	Header 5
100	A	3	1
100	B	1	1
100	C	2	1
100	A	1	2
100	B	1	2
100	C	2	2
100	A	2	3
100	B	3	3
100	C	3	3

For a single EmpID, I have 3 KPIs ( A,B,C but this will be different for other EmpIDs; may be more or less than 3). For each KPI, I have  some Status: 3 being MAX and 1 being MIN. I have 3 months now for this particluar Employee. For each month. EmpID has some Status.

My requirement is:

Find the number of KPIs for an employee for each month.
Find the sum of the status for each month
Set the color based on certain thresholds

For Example

Let us say the person has 3 KPIs

• Min Value = 3 * 1 = 3   (No. of KPI * Status Value)
• Mid Value = 3 * 2 = 6
• Max value = 3 * 3 = 9

  Now to create a color indicator (graph) based on values calculated in Min, Mid and Max. (Here different for different EmpIDs)

KPI Status (suppose for Month 1)

KPI 1 (A) – 3
KPI 2 (B) – 1

KPI 3 (C) – 2

Sum = 6

Min Value -> Red

Min-Mid -> Yellow

Mid-Max -> Green

So '6' will fall in Mid-Max i.e Green.

So, If I select EmpID = 100, For Month 1 (for all KPIs), The summation will be Green

Regards!

[christophebrault]

Hi,

in a straight table with EmpID and month as dimensions i wrote this expression for the color:

if(sum(Status)= count(DISTINCT KPIs)*1,red(),

  if(sum(Status)< count(DISTINCT KPIs)*2,Yellow(),green()

  ))

Hope this will match your needs

[dmohanty]

Thank you Christophe for quick understanding and implementation.

Can it be represented in a Gauze format or Traffic Light format, instead of setting as background color?

Regards!

[christophebrault]

You can write as an expression :

if(sum(Status)= count(DISTINCT KPIs)*1,'qmem://<bundled>/BuiltIn/led_r.png',

  if(sum(Status)< count(DISTINCT KPIs)*2,'qmem://<bundled>/BuiltIn/led_y.png','qmem://<bundled>/BuiltIn/led_g.png'

  ))

and choose "picture" as representation

[dmohanty]

Yes Christophe,

Many thanks for this help. I can achieve as suggested.

But will it be feasible to show outside of the chart? Like in a Traffic Light Gauze chart or something?

I mean to say, when we will click a value from  Month List Box, that Light will change color as per condition!

Regards!

[christophebrault · Accepted solution]

Hi,

look at the attached file for an example in traffic gauge outside the chart.

But its working only if you select one EmpID and one Month

[dmohanty]

Hi Christophe,

Thank you very much for this help! Understood the logic.

Yes ur correct, only one selection can be done at a time. Bettter to enable the 'Always one selected value' option.

Regards!

[christophebrault]

Ok nice.

You could use a condition on the traffic light chart to show it only when count(DISTINCT EmpID)=1 and count(DISTINCT Month)=1