Complete novice - List all values for unique value

DATA:

I have a set of data that is showing transaction times for a particular process for a number of users. i currently have 1 day and approx 200k rows and data is structured as follows:

Objective:

I want to be able to see the average / distribution of the time it takes by CNTR_NBR - e.g. what is the example time "Exam" vs "BUNC" takes but equally the distribution / extremities. Equally a separate goal (but not yet) would be is Georgeweasley slower at processing "BUNC" than "Ronweasley" is...

Question:

I have been able to trim my cntr_nbr (as the original data actually has a number of none required characters after each customer), I have been able to add processing time using multirow formula and expression:

tonumber (
IF [USER_ID]=[Row-1:USER_ID]
Then DateTimeDiff([BEGIN_DATE],[Row-1:BEGIN_Date],"seconds")
ELSE '0'
ENDIF )

In order to achieve my objective i think my option is:

Pivot, list or transpose my data so that I have each unique CNTR_NBR as a new column, and show ALL processing time values in the rows beneath, then I can analyse each column separately.

However i am struggling to cross tab / transpose to LIST all values not summarise?

i.e. below is the current progress I have made - as you can see I have each unique CTNR_NBR as a column header, and I have listed all my values but because it is a concatenate its no use to me currently.....

current output.PNG

Equally if i am missing an easier method i would appreciate any advice!

Developer

Tips and Tricks

Accepted answers

danilang

Hi @noobteryx (great user name...but what are you going to when you become an ACE)

Judging from your workflow, you already have to change your name. Using the Cross Tab tool will automatically award you house points. Here's some more tips

You can do most of the analysis of the first part of question by using the functions available in the Summarize tool. This frees you from having to duplicate formulas to generate your stats for each column after you Crosstab. I've configured it to generate average, min, max, standard deviation, and the 25th, 50th and 75th percentiles.

For the user based rankings, use the cross tab with the users as rows giving

Note: that I also modified your calculation of the Processing time to remove the noise values when you move from one user to the next.

Dan

DataAnalysis.yxmd

All comments

noobteryx

Update:

I added a row ID and then crosstab using this and have broadly got it to work (see picture below):

current output2.PNG

If possible i would like to remove the nulls, and also somehow add a formula to remove values >X and <Y......

danilang

Hi @noobteryx (great user name...but what are you going to when you become an ACE)

Judging from your workflow, you already have to change your name. Using the Cross Tab tool will automatically award you house points. Here's some more tips

For the user based rankings, use the cross tab with the users as rows giving

Note: that I also modified your calculation of the Processing time to remove the noise values when you move from one user to the next.

Dan

DataAnalysis.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703