Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a straight table that looks as below, and I need to add some conditions and (if possible) highlight the rows Green/Red/Amber dependent on the following criteria. If it is not possible to highlight the rows, perhaps add a new column as 'RAG Status' and have G/R/A inputted depending on the result.
Criteria:
GREEN - Actual Start/End Time is less than 5 minutes of the Scheduled Start/End Time
AMBER - Actual Start/End Time is more than 5 and less than 30 minutes of the Scheduled Start/End Time
RED - Actual Start/End Time is more than 30 minutes of the Scheduled Start/End Time
Table:
Name | Description | Scheduled Start Time | Scheduled End Time | Actual Start Time | Actual End Time |
---|---|---|---|---|---|
Run1 | ABC | 07:30:00 | 07:45:00 | 07:34:00 | 07:46:00 |
Run2 | 123 | 18:00:00 | 18:20:00 | 18:15:00 | 18:24:00 |
Run3 | XYZ | 09:45:00 | 10:05:00 | 09:55:00 | 10:22:00 |
Run4 | 456 | 12:05:00 | 12:30:00 | 12:05:00 | 13:08:00 |
So in this instance, the results would be as follows:
Run1 - Green - Times are within the criteria
Run2 - Amber - Starts more than 5 minutes after Scheduled Start Time
Run3 - Amber - Starts and Ends more than 5 minutes after Scheduled End/Start Times
Run4 - Red - Ends more than 30 minutes after Scheduled End Time
Thanks and kind regards,
Tristan
Looking for this:
Try this script to create color flag
Table:
LOAD Name,
Description,
[Scheduled Start Time],
[Scheduled End Time],
[Actual Start Time],
[Actual End Time],
If([Actual Start Time] - [Scheduled Start Time] < (5/(24*60)) and [Actual End Time] - [Scheduled End Time] < (5/(24*60)), 1,
If([Actual Start Time] - [Scheduled Start Time] > (5/(24*60)) and [Actual End Time] - [Scheduled End Time] > (5/(24*60)), 3, 2)) as ColorFlag
FROM
[https://community.qlik.com/thread/169976]
(html, codepage is 1252, embedded labels, table is @1);
and then use this to give color to each of your dimension and expression. Attaching the qvw for reference purposes.
HTH
Best,
Sunny
Follow this:
for every Dimension or expression give the condition like
if( Starts>5, Green()) ("Give your actual expression its a just example only")as so on.
You cannot do in a table, use instead a straight table, in the expressions there are , click and in the background or text item menu add your condition
E.g.: if a<b, RGb(255,0,0), if(a < c, rgb(0,255,0), rgb 100,100,100))
Hi Tristan,
You can do this simply with 'visual Cues' tab.
Click right mouse on the table,
select properties,
select visual cues,
and you can introduce the logic a an expression. and select the color criteria as per the requirement.
The 'apply'
Rgds.
Priyantha.
Looking for this:
Try this script to create color flag
Table:
LOAD Name,
Description,
[Scheduled Start Time],
[Scheduled End Time],
[Actual Start Time],
[Actual End Time],
If([Actual Start Time] - [Scheduled Start Time] < (5/(24*60)) and [Actual End Time] - [Scheduled End Time] < (5/(24*60)), 1,
If([Actual Start Time] - [Scheduled Start Time] > (5/(24*60)) and [Actual End Time] - [Scheduled End Time] > (5/(24*60)), 3, 2)) as ColorFlag
FROM
[https://community.qlik.com/thread/169976]
(html, codepage is 1252, embedded labels, table is @1);
and then use this to give color to each of your dimension and expression. Attaching the qvw for reference purposes.
HTH
Best,
Sunny
Hi Sunny,
Thanks very much for this! It's really close to what I'm going for. I have three questions:
1) Unfortunately my Actual Start/End Times and Scheduled Start/End Times are from different tables so the lines in the script do not work - is there anyway I can reference two of the columns from a different table?
e.g.
Scheduled_Data:
LOAD A as Name,
B as Description,
C as [Scheduled Start Time],
D as [Scheduled End Time],
and
Actual_Data:
LOAD A as [Actual Start Time],
B as [Actual End Time],
2) For the second line in the script, I need it to be Amber if the Actual Start OR End Times are >5 of the Scheduled Start/End Times - and for Red the same - Actual Start OR End Time is more than 30 minutes of the Scheduled Start/End Time
3) Could you explain the formula (5/(24*60)?
Any further help would be really appreciated
Best,
Tristan
1) Do you have some common fields between the first and second table? I am sure you do, else the comparison just won't make sense. If my assessment is right, just join the two tables together and then do the calculation in a combined table:
TableA:
LOAD *
FROM xyz
Join(TableA)
LOAD *
FROM abc
NewTable:
LOAD *,
aboveCalculation
Resident TableA;
DROP Table TableA;
2)So Amber is the last part of the nested if statement, if Start Difference is less than 5 and End Difference is less than 5 do red. second if is if Start Difference is greater than 30 and End difference is greater than 30 do red. and if both statements are not true than do Amber which is the scenario you explained above
3) (5/(24*60)) is the number equivalent of 5 minutes
I hope all of the above make sense. Let me know if you still have questions.
Best,
Sunny
Hi Sunny,
Thanks again for your reply.
1) That's correct, there is a common field (Name), but I still can't get it to work. It looks something like this:
Scheduled_Data:
LOAD A as Name,
B as Description,
C as [Scheduled Start Time],
D as [Scheduled End Time],
;
Actual_Data:
LOAD A as Name,
A as [Actual Start Time],
B as [Actual End Time],
;
Inner join(Scheduled_Data)
LOAD
Name
RESIDENT Actual_Data
;
NewTable:
LOAD
If([Actual Start Time] - [Scheduled Start Time] < (5/(24*60)) and [Actual End Time] - [Scheduled End Time] < (5/(24*60)), 1,
If([Actual Start Time] - [Scheduled Start Time] > (5/(24*60)) and [Actual End Time] - [Scheduled End Time] > (5/(24*60)), 3, 2)) as ColorFlag
RESIDENT Scheduled_Data;
But this returns an error on finding Actual Start Time and Actual End Time. Do you know where I've gone wrong with it?
2) Sorry I think I haven't explained the criteria very well. I was hoping to have it so:
Green if the Start and End differences were <5
Amber if the Start or End differences were >5
Red if the Start or End differences were >30
3) Ah okay I see! So if I wanted to change it to >10 minutes, I would just change the formula to (10/(24*60))?
Thanks again for the help.
Kind regards,
Tristan
1)
Try this script:
Scheduled_Data:
LOAD A as Name,
B as Description,
C as [Scheduled Start Time],
D as [Scheduled End Time],
Join (Scheduled_Data)
LOAD A as Name,
A as [Actual Start Time],
B as [Actual End Time],
NewTable:
LOAD *,
If([Actual Start Time] - [Scheduled Start Time] < (5/(24*60)) and [Actual End Time] - [Scheduled End Time] < (5/(24*60)), 1,
If([Actual Start Time] - [Scheduled Start Time] > (5/(24*60)) and [Actual End Time] - [Scheduled End Time] > (5/(24*60)), 3, 2)) as ColorFlag
RESIDENT Scheduled_Data;
3) You are bang on target with this one.
2) I will get back to you on this one after testing it out.
Best,
Sunny
I think this maybe works for the 2nd question
If([Actual Start Time] - [Scheduled Start Time] < (5/(24*60)) and [Actual End Time] - [Scheduled End Time] < (5/(24*60)), 1,
If([Actual Start Time] - [Scheduled Start Time] > (30/(24*60)) or [Actual End Time] - [Scheduled End Time] > (30/(24*60)), 3,
If([Actual Start Time] - [Scheduled Start Time] > (5/(24*60)) or [Actual End Time] - [Scheduled End Time] > (5/(24*60)), 2,))) as ColorFlag