How to sort the rows in the PivotTable

[Stanley5487]

pv <- qpvt(data, rows = "party", columns = "gender", calculations = "round(mean(revenu))") pv$sortRowDataGroups(orderBy = "calculation") pv$renderPivot()

This is my code, I want to sort the data by the total "calculation"(revenue mean)belonging to the "male" in the columns "gender"; however, it just can sort by the total "calculation"(revenue mean). How can I fix it?

[cbailiss]

This is more involved and can't be done with the quick pivot functions (e.g. qpvt).
To sort just by male, you need to define an additional (hidden) calculation that always returns the values for male, then use this calculation to sort on. The new calculation is specified with a filter override that always returns calculates the value for male.

Example code - sorting by the male value in descending order (highest first):

party = c(1, 2, 1, 2, 1)
gender = c("M", "M", "F", "F", "F")
revenu = c(25, 20, 5, 20, 10)
data = data.frame(party=party, gender=gender, revenu=revenu)

library(pivottabler)
pv <- PivotTable$new()
pv$addData(data)
pv$addColumnDataGroups("gender")
pv$addRowDataGroups("party")
pv$defineCalculation(calculationName="MeanRevenu", summariseExpression="round(mean(revenu))")
onlyMaleFilter <- PivotFilterOverrides$new(pv, variableName="gender", values="M", action="replace")
pv$defineCalculation(calculationName="CustomSort",
                     filters=onlyMaleFilter,
                     summariseExpression="round(mean(revenu))", visible=TRUE)
pv$evaluatePivot()
pv$sortRowDataGroups(calculationName = "CustomSort", sortOrder = "desc")
pv$renderPivot()

This generates the following:

The values in the "CustomSort" columns always show the calculation value for male, even under the "F" and "Total" column headings.

The custom calculation can then be hidden by changing the visible=TRUE to visible=FALSE in the code above:

[Stanley5487]

Thank you!! I will try it tomorrow