10 Replies Latest reply on Nov 12, 2019 10:37 AM by Zen Tableau

# Single Dimension has differnt values types 'status' needs to compare data capture and count day

Hi All,

How are you.

Tried various ways but no luck.The scenario where I have to count dates based upon 'StatusType' like below scenario ID 100. I have Count the days between Statustask. I have attached a sample data excel file

100 : Start    -  Completed = 10 days

Begin    -  Completed = 10 days

Begin    -  Completed = 04 days

Begin    -  Completed = 05 days

Same applies for 101 records (ID)

Desired    OUTPUT    - for ID 100 only

Yellow    14    days

Orange    15    days

Thanks

cs

• ###### 1. Re: Single Dimension has differnt values types 'status' needs to compare data capture and count day

How do you determine which is yellow/orange? There does not appear to be any sort of identifier.

• ###### 2. Re: Single Dimension has differnt values types 'status' needs to compare data capture and count day

Hi Bryce,

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)

Start=>Completed=>Begin=>Completed=>Begin=>Completed=>Begin=>Completed

100 : Start    -  Completed = 10 days

Begin    -  Completed = 10 days

Begin    -  Completed = 04 days

Begin    -  Completed = 05 days

Thanks

cs

• ###### 3. Re: Single Dimension has differnt values types 'status' needs to compare data capture and count day

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!

Best,

Bryce

• ###### 4. Re: Single Dimension has differnt values types 'status' needs to compare data capture and count day

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.

• ###### 5. Re: Single Dimension has differnt values types 'status' needs to compare data capture and count day

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

The Final Output( Desired Output)

Sample data overall columns

appreciate your inputs.Thank you once again

Thanks

Sekhar

• ###### 6. Re: Single Dimension has differnt values types 'status' needs to compare data capture and count day

Ah! I think that makes sense. So it's not really start/begin to completed. The original post makes it appear as such and alternating between each cycle as yellow/orange.

So it's really start/begin to complete = internal; complete to start/begin = external.

Is that correct? Example:

• ###### 7. Re: Single Dimension has differnt values types 'status' needs to compare data capture and count day

If the above is right, then maybe the attached Table (2) is closer.

Result with the same data:

• ###### 8. Re: Single Dimension has differnt values types 'status' needs to compare data capture and count day

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

Thanksa

• ###### 9. Re: Single Dimension has differnt values types 'status' needs to compare data capture and count day

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.

• ###### 10. Re: Single Dimension has differnt values types 'status' needs to compare data capture and count day

Thanks a million Bryce, really appreciate your help and inputs. Thanks once again