Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Anonymous
Not applicable

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

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:

Sample.png

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? 

Labels (1)
2 Replies
Highlighted
Contributor III
Contributor III

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;

 

Highlighted
Digital Support
Digital Support

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.