A solution is to create a new variable to use for labeling instead of the Time field - and make all other times beside the red values null.
Based on value, something like:
IF [Time]=1600 THEN [Time]
ELSEIF [Time]=2330 THEN [Time]
Use this variable as the pill in your Label field for your pie chart.
Alternatively, if you wanted to do it based on a specific value (like volume), you could do it using the same technique, like:
IF [Volume]<1000 THEN [Time]
Hope this helps!
But the red color in the chart will be dynamic...the color will change as and when the database table has not been updated.
So it may not be that we only color the 1600 or 2330 hrs daily.
To make it dynamic, you'll have some kind of field that represents whether or not the database table has been updated. Once you've created it, it will show up in your example above that times 1600 and 2330 are both "No", and all other times are "Yes". Call this field [DatabaseUpdateCheck]. Then:
IF [DatabaseUpdateCheck]="No" THEN [Time]
If you have issues creating that field, if you create a sample workbook I can help figure out your specific situation.
Attaching the workbook.
Also see if a particular time of day has not passed then it would be colored orange. On completion it will be colored green. If the file has not been updated at end of the day then it has to be colored red.
So I am looking at 3 color variants:
Green - Complete
Orange - In Progress
Red - Incomplete
PieQuestion.twbx.zip 74.5 KB
Great, thanks for sending. I'm almost there Just one more question - how do you know whether a file is Complete, In Progress, or Incomplete?
What I'm guessing is that:
1. A file is Complete if FileSize field is populated, right?
2. A file is In Progress if the FileSize field is not populated, and it is a file with today's date?
3. A file is Incomplete if the date is before today, and the FileSize is not populated?
So with the above assumptions, this is what I'd use:
IF [FileSize]>0 THEN "Green"
ELSEIF (Date([Date])>=Today()) AND (ISNULL([FileSize]) OR [FileSize]=0) THEN "Orange"
But please let me know if different, I can continue to tweak!
Thanks....the coloring is to be based on comparison with the system time. The FT_Time field .
Can you please help with the updated workbook for the calculation?
Thanks a lot for your help
How is the current time being compared with the FT_Time field? If you can tell me what logic around each of:
1. Complete files
2. In Progress files
3. Incomplete files
then I can answer. What are the rules for each group?
IF ISNULL([Time]) THEN 'Red'
This is the logic that I am using, now I want to compare the orange color with current time.
If the time field is NULL even after the current time is surpassed then it is to be colored red else yellow.
But if the Time field has data then green