topic Re: Pivot Table - Sort top article per month in QlikView

Pivot Table - Sort top article per month

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

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!

Re: Pivot Table - Sort top article per month

vinieme12 — Thu, 29 Dec 2016 13:50:37 GMT

can you post the app here?

Re: Pivot Table - Sort top article per month

Anonymous — Thu, 29 Dec 2016 14:10:34 GMT

Here the .qvw

Re: Pivot Table - Sort top article per month

rbecher — Thu, 29 Dec 2016 15:02:47 GMT

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

Re: Pivot Table - Sort top article per month

sunny_talwar — Thu, 29 Dec 2016 15:20:54 GMT

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

Re: Pivot Table - Sort top article per month

Anonymous — Thu, 29 Dec 2016 15:30:48 GMT

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

QV11.20SR15

Re: Pivot Table - Sort top article per month

Anonymous — Thu, 29 Dec 2016 15:33:55 GMT

Nice

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

Thx,

Roland

Re: Pivot Table - Sort top article per month

sunny_talwar — Thu, 29 Dec 2016 15:36:52 GMT

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

Re: Pivot Table - Sort top article per month

sunny_talwar — Thu, 29 Dec 2016 15:38:58 GMT

Implemented the 2 suggestion here

New Expression:

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

Re: Pivot Table - Sort top article per month

rbecher — Thu, 29 Dec 2016 15:40:06 GMT

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).

Re: Pivot Table - Sort top article per month

Anonymous — Thu, 29 Dec 2016 15:49:46 GMT

Wow, that's interesting Exactly what I want!

Have you talked to someone "official" if this is how it will work in all future releases?

Wenn was it changed at all? Was the sorting changed in QV12.00 Initial Release?

Danke für deinen Einsatz heute! Guten Rutsch & VG nach Deutschland!

Roland

Re: Pivot Table - Sort top article per month

Anonymous — Thu, 29 Dec 2016 16:08:04 GMT

One follow up question:

Any chance to sort the pivottable when the Month is in the columns.

Again I can achieve this using my "Aggr"-Dynamic Dimension, but I would love to avoid it!

See again attached .qvw. !

Re: Pivot Table - Sort top article per month

rbecher — Thu, 29 Dec 2016 17:14:32 GMT

Found it myself by playing with the options. I think if someone submits this issue (unexpected behavior) this might change in the future. So, still not reliable but a temporarily workaround..

VG und einen angenehmen Jahreswechsel nach Österreich!

Ralf