Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Pivot Table - Sort top article per month

Hi!

I started the discussion here https://community.qlik.com/blogs/qlikviewdesignblog/2016/12/12/pareto-analysis

I thought I have found a better solution for the following question: In need a Pivottable that shows the "top articles" in each Month of the Year. The sort order should be:

- Sort YEAR numerically ASC

- Sort Month numerically ASC

- Sort Articels by sum (Sales) in each month. (highest Sales first)

I have created a small example .qvw here: content.heldendaten.eu/SortPivot_TopArticlePerMonth.zip

My current solution (see yellow box in the screenshot) is quite wacky, and I hope that someone has a better solution (maybe with the new sortable aggr?)

Sorting all 3 dimensions by y-Value does not help, as then year and month get sorted the wrong way!

2016-12-29 14_32_35-QlikView x64 - Testversion - [C__Users_rva_AppData_Local_Temp_Temp1_SortPivot_To.png

1 Solution

Accepted Solutions
rbecher
MVP
MVP

I agree: a BI tool must have reliable sorting options! Which we do not have in full here..

But, you can set Year and Month sorting to DESC (don't know why this seems to be the opposite than expected) and then set sort by y-Value on Year (see attached).

Astrato.io Head of R&D

View solution in original post

12 Replies
vinieme12
Champion III
Champion III

can you post the app here?

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

Here the .qvw

rbecher
MVP
MVP

I think sort by "y-Value" is the only usefull way..

Astrato.io Head of R&D
sunny_talwar

One solution is to create a new expression

RangeSum(Jahr + Monat/1E5 - Sum(Sales)/1E10)

Which you can then hide using the following macro

SUB Squeeze

  CALL HideColumn("CH04", 4)

END SUB

PRIVATE SUB HideColumn(ch, n)

  SET ch = ActiveDocument.GetSheetObject(ch)

  ch.SetPixWidth (n-1), 2

END SUB

Capture.PNG

Anonymous
Not applicable
Author

Hi Ralf!

I agree that sorting in QV12.10 SR1 via "y-Value" is better than it was in QV11.20SR15.

At least the year+month seem to stay in DESC order. See the two screenshots in comparision.

However in my special case it is a customer bugging me for years, that a BI-Tool should be able to

- Sort Year+Month ASC

- Sort Articles by sum(Sales) DESC

So probably for both of us the Sort in QV12.10SR1 is good enough, but not for my customer

QV12.10SR1

2016-12-29 16_26_10-QlikView x64 - Testversion - [C__Users_rva_Desktop_SortPivot_TopArticlePerMonth..png

QV11.20SR15

QV1120SR15.png

Anonymous
Not applicable
Author

Nice

Unfortunately the hidden column is added when this is exported to Excel. Any idea how to overcome this issue?

Thx,

Roland

sunny_talwar

I can think of two ways to handle this....

1) Use macro to export the chart where Macro can handle which columns to export

2) Use Dual function to show blank space instead of the numbers. The column will still export, but hopefully it will be an empty column

sunny_talwar

Implemented the 2 suggestion here

New Expression:

Dual(' ', RangeSum(Jahr + Monat/1E5 - Sum(Sales)/1E10))

rbecher
MVP
MVP

I agree: a BI tool must have reliable sorting options! Which we do not have in full here..

But, you can set Year and Month sorting to DESC (don't know why this seems to be the opposite than expected) and then set sort by y-Value on Year (see attached).

Astrato.io Head of R&D