Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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).
can you post the app here?
Here the .qvw
I think sort by "y-Value" is the only usefull way..
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
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
Nice
Unfortunately the hidden column is added when this is exported to Excel. Any idea how to overcome this issue?
Thx,
Roland
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
Implemented the 2 suggestion here
New Expression:
Dual(' ', RangeSum(Jahr + Monat/1E5 - Sum(Sales)/1E10))
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).