Spotfire - Pivot table - MostCommon (Mode) - Count of MostCommon result

1
votes

I have a question regarding my Spotfire model.

In the Pivottable which we have formed, we have agents, we have an average column in the Pivot table for the sales quantity and a 'MostCommon' sales quantity value per Agent.

Now, as another expression, I want to add a column counting the MostCommon value per Agent? Can anyone give some guidance?

So finally we will display the agent, the average sales per agent, the most common sales quantity, then a count of how many times the most common sales value occurs per agent.

Kind regards

1
pivotpivot-tablespotfire
Should your additional column should be based off the pivoted data, or the raw data? – scsimon
Hi there. Originally thought it would be possible in the Pivot, although have resorted to added calculated columns, works perfectly. I was missing the "over Agents" step below in the solution. – user4242750

1 Answers

2
votes

@user4242750 - I have added 'most common' column in the raw data table with the expression below.

MostCommon([Sales]) over ([Agent])

Based on this column, created another column ' count_mostcommon' which puts 1 if it finds the most common value in sales column.

if([Sales]=[most common],1,null)

Created a cross table from this raw table and added avg(sales) and count(count_mostcommon) aggregations.

most common sales

Note: Created 'most common' column for explanation purpose. You can avoid creating it as a separate column and can include the expression directly in 'count_mostcommon' column expression as shown below

if([Sales]=MostCommon([Sales]) over ([Agent]),1,null)