Jump to content
We've recently updated our Privacy Statement, available here. ×

Percentage of Wins/Lossess Against Competitors


Huskir Villeneuve

Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...