topic Re: Sorting in Pivot Table by Values when One of the Dimensions is Dragged into Horizontal Row in QlikView

Sorting in Pivot Table by Values when One of the Dimensions is Dragged into Horizontal Row

Anonymous — Sat, 16 Nov 2024 01:40:35 GMT

I have a Pivot Table, where there are Few Dimensions and Time Period Available. However, the visual representation of that will be in the below format:

I have Dragged the Year Time Period into Horizontal placement, which makes it easier to view the numbers across Time Period.

However, I would like to Sort the Values by 2019. I tried by choosing 'Y-Value' as the Sort option. But the option considers the entire (2019+2018+2017+2016) instead of just 2019.

Is there a way to achieve this?

Re: Sorting in Pivot Table by Values when One of the Dimensions is Dragged into Horizontal Row

gavinlaird — Fri, 20 Dec 2019 23:59:09 GMT

You could set it to sort by an expression instead of Y-value, and make the sort expression the same as what is used in the table, but add set analysis to only count the current year. If the expression in your table is "sum(Amount)", you would make the sort expression "sum({<TimePeriod={$(vMaxTimePeriod)}>} Amount)", where vMaxTimePeriod is a variable storing the max Time Period.

You can set vMaxTimePeriod in the load script with code similar to:

Temp_maxTimePeriod: LOAD max(TimePeriod) as maxTimePeriod Resident [your data table containing TimePeriod]; Let vMaxTimePeriod = Peek('maxTimePeriod'); Drop Table Temp_maxTimePeriod;

Re: Sorting in Pivot Table by Values when One of the Dimensions is Dragged into Horizontal Row

Brett_Bleess — Mon, 23 Dec 2019 20:10:46 GMT

Did the post help you with things? If so, be sure to return to things and use the Accept as Solution button to give the poster credit for the assistance and let other Community Members know what worked. They are definitely on the right track with things, I am going to include a link to the Design Blog area for you in case you want to further search there, might want to have a look at some of the Set Analysis posts there I think.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett