Percentage of Wins/Lossess Against Competitors

[Huskir Villeneuve]

I have a data table which is in the following format:

 

 

 

Product

Experiment 1

Experiment 2

Experiment 3

 

 

Competitor 1

x1

y1

z1

 

 

Competitor 2

x2

y2

z2

 

 

Competitor 3

x3

y3

z3

 

 

Prototype 1

x4

y4

z4

 

 

Prototype 2

x5

y5

z5

 

 

Prototype 3

x6

y6

z6

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I'm trying to calculate in how many Experiments each one of my prototypes beats the competitor (that is: prototype value > competitor value in each experiment). I'll display this data in a bar chart in which the X-Axis is the Competitor name and the Y-Axis is how many times my prototype (filtered in a drop-down document property) beats each one of the competitors.

 

I have tried multiple transformations (unpivot, pivot) and haven't found a way in which I can make this comparison and show the data in a bar chart. My closest attempt was to unpivot the data in the following format and create a column with the value for each competitor, then compare my prototypes to this column. But I didn't succeed in having the right value of the competitors associated with their respective experiments (underlined in the table below), meaning that I can only get competitor values in lines in which the product is the competitor. Is there a way to have the competitor value repeated in all lines, just associated with the Experiment OVER function wouldn't work because I'm not doing any concatenation.

 

 

 

Product

Experiment

Value

Competitor 1

Competitor 1_Win/Loss

 

 

Prototype 1

Experiment 1

x4

x1

if(x4>x1,1,0)

 

 

Prototype 1

Experiment 2

y4

y1

if(y4>y1,1,0)

 

 

Prototype 1

Experiment 3

z4

z1

if(z4>z1,1,0)

 

 

Prototype 2

Experiment 1

x5

x1

if(x5>x1,1,0)

 

 

Prototype 2

Experiment 2

y5

y1

if(y5>y1,1,0)

 

 

Prototype 2

Experiment 3

z5

z1

if(z5>z1,1,0)

 

 

 

I'm stuck with this problem and can't seem to find a way around it. Any help is appreciated.

Thank you.

[Khushboo Rabadia]

You can achieve this requirement using data wrangling itself. You would not require OVER function.

I have attached dxp showing the same. Note, it is in 10.3 version

I am not sure how your underlying data source is so I have used two data tables approach and performing left outer join to bring in competitor's data

Source information for table 1:

2. Select Data > Transform data...

Added transformations

Transformation name: Calculate new column

Column name: Prototype

Expression: case when Find("prototype",lower([Product]))0 then [Product] end

 

Transformation name: Calculate new column

Column name: Match

Expression: case when Find("competitor",lower([Product]))0 then "1" endSource information for table 2:

1. Select Data > Add...

Source: Data table from current analysis

Data table: Data Table

Update behavior: Automatic

Added transformations

Transformation name: Pivot

Row identifiers: Prototype

Value columns and aggregation methods:

First(Experiment 1)

First(Experiment 2)

First(Experiment 3)

Column titles: (None)

Column naming pattern: %V

Transfer columns and aggregation methods: (None)

Transfer column naming pattern: %A(%T)

 

Transformation name: Unpivot

Columns to pass through: Prototype

Columns to transform:

Experiment 1

Experiment 2

Experiment 3

Category column name: Category

Category column data type: String

Value column name: Value

Value column data type: String

Empty values included: Yes

 

Transformation name: Calculate new column

Column name: Match

Expression: "1"

 

Data loaded at: 4/1/2020 4:13 PM

Data was added as a new data table

2. Select Data > Transform data...

Added transformations

Transformation name: Filter rows

Expression: [Prototype] is not null

 

3. Select Data > Add...

Added transformations

Transformation name: Unpivot

Columns to pass through:

Product

Match

Columns to transform:

Experiment 1

Experiment 2

Experiment 3

Category column name: Category

Category column data type: String

Value column name: Value

Value column data type: String

Empty values included: Yes

 

Data was added as new columns in data table 'Data Table (2)'

Matching behavior: Tries to match the specified columns when data is loaded

Matched columns:

Match Match

Category Category

Added columns:

Product

Value

Ignored columns: Prototype

Join method: Left outer join

Treat empty values as equal: No