How do you determine which is yellow/orange? There does not appear to be any sort of identifier.
Thank you, appreciate your response. Actually, it's 'Status Type' alternate values takes different color, which is the count of dates.. it works in this manner..say for instance I have ID-100 value has multiple rows and there are multiple 'status types' which I break down below
Each unique ID has max 3 types(start,begin,completed) which revolve these days counts and the alternate number goes into other 2 colors in yellow and oranges below I mentioned
(Y)- Yellow (O) - Orange
(Y) (O) (Y) (O)
100 : Start - Completed = 10 days
Begin - Completed = 10 days
Begin - Completed = 04 days
Begin - Completed = 05 days
Hope I answered your question. Appreciate your inputs
Gotcha. That's what I thought, just makes it much more complicated.
I was able to create the table view using a few table calculations.
After creating this, it was figuring out how to use those table calculations to do the SUM by each ID/color. It got complicated because you are unable to use other table calculations in the "Compute Using" section of a table calc.
In the end, I was able to right click and "Hide" on "StatusGroupLast2" where False and where "StatusNBR"=1. Then I simply head most of the headers to get down to this viz:
I believe this is what you want! You can, of course, filter it by ID if desired. I have to do my real work for now, but I've attached it for you to have a look at and try to work through. I can try to explain some things later if you have questions. Definitely was a good challenge!
StatusAltColor_v2018.1.twbx 36.3 KB
One note: you don't need the first column I have - it just made it easier to visualize it as it would otherwise group have group based on ID and then the Date - slightly more confusing since it'd group the first Completed and second Begin together having both been on 1/20.
Not important, just wanted to mention why I added that in there.
Thanks Bryce Larson,Thanks a million really appreciate your inputs but it has small glitch.I have broken down with how count is broken down.Hope I conveyed it properly
The 'Orange' and 'Yellow' colors are fictional columns for final output. This indicates as 'Internal and External' woks days which has been recorded.
- Internal = Yellow
- External = Orange
Each ID has max of 3 types of Status Type 'Start,Completed,Begin'
The days count are great but it works in below image fashion. Hope I didn't confused you. My apologies. Below is the pattern of the days count. It basically tracks the work which happen externally and internally and counts the dates which overall both date counts
Please find below count process with number
Sample data overall columns
appreciate your inputs.Thank you once again
Perfect Bryce,really appreciate it..you are a star.lastly in the day counts
Day the count, needs weekend excluded. We have to remove weekend count
Much trickier! If you know your dates are always weekdays, you can do a DATEDIFF using "week" and multiply it be 2 in order to count the number of weekend days. Subtract that amount from the previous value we calculated to get # of weekdays.
This is the only way I can think to do it without bringing in a Calendar table.
Thanks a million Bryce, really appreciate your help and inputs. Thanks once again