Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
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!
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
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!
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
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!
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