topic Sorting a pivot in QlikView

Sorting a pivot

nkosinde13 — Wed, 25 Nov 2020 16:16:04 GMT

Good day community

I have another challenge where I need to sort a pivot table using expressions. I know this works in a straight table and not a pivot. I've looked through the Qlikview pages and I cant seem to find any example that is easier to understand. So basically what I want is that a selection of a list box needs to sort the pivot table on expression. Currently the selection shows and hides the table, meaning I have 5 different pivot tables that show and get hidden depending on the selection. So I want the same selection to also sort the table by expression when it gets displayed e.g. If a user selects Budget Var %, the chart needs to sort by Budget Var % column either ascending or descending. Also note that the expressions are Cyclic as well.

Please see the attached screenshot for details and any help is greatly appreciated.

Thanks

Matlotlo

Re: Sorting a pivot

florentina_doga — Mon, 21 Oct 2019 09:01:29 GMT

in tab sort

biff expresion

Re: Sorting a pivot

nkosinde13 — Mon, 21 Oct 2019 10:17:01 GMT

Hi Florentina

Thank you for the quick response.

kindly note that I did put in the expression on Sort tab but it's not working. My expression is as follows and this is exactly what I put on sort tab: Or do you mean I should modify the expression on sorting to something else?

Sum({<$(eSetFYTD_CN)>}Amount) / $(ThouMenu)

(Sum(Budget) / $(ThouMenu))

Thanks

Matlotlo

Re: Sorting a pivot

atoz1158 — Mon, 21 Oct 2019 10:45:11 GMT

I found that I needed to wrap the Expression inside the AGGR() function using the dimensions of the Pivot table.

Give that a try.

Regards

Adrian

Re: Sorting a pivot

Brett_Bleess — Tue, 29 Oct 2019 12:52:43 GMT

Matlotlo, did Adrian's post help you get things working as you wish? If so, do not forget to come back and give credit by using the Accept as Solution button on the post and to let others know things did work. If you still need further help, leave an update on where you are with things.

Here is a Help link that may be helpful in this case regarding the AGGR function Adrian mentioned, it may help you get the hang of things if you are still working on them:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/NestedAggregations/sum-of-rows-pivot-tables.htm

Regards,
Brett