Using an ID as Sort Expression on Pivot Table

[#1]

Hi,

I created our Manufacturing Report on Qlikview 10 SR1 a few days ago and have the following problem:

As there are lots of KPIs (+/- 80) which should be sorted by a predefined order. I created an additional column with an ID per KPI therefore. E.g.:

Company	KPI-ID	KPI-Desc	Value
A100	2	Set up Avg Time	x
A100	4	Total Waste	x
A100	1	Speed in Run Time	x
A100	3	Run Time	x
A102	5	Budgeted working hours	x
A102	3	Run Time	x
A102	1	Speed in Run Time	x
A102	6	Set up (tool exchange)	x

Simplified I want to show just the KPI-Desc and the Value per Company which works fine. But unfortunatelly the correct order does not work, if I set the KPI-ID as Sort Expression for the KPI-Desc (neither if I use e.g. "avg(KPI-ID)" or sum,min,max... etc.). It works if I add the KPI-ID as additional column in the table, but that is not my target. I don't want to see the ID in the report.

Can anybody help?

Thanks

[#2]

you should sort ID by expression and paste there

=min(KPI-ID)

[#3]

Hi, Thanks for your quick answer.

But this is exactly what I'm trying without success. I'm on the "Sort" tab, mark "Sort by Expression" and add =min(KPI-ID) there. It doesn't work. If I add another Dimension with the KPI-ID it works well, but that's what I don't want...

[#4]

send snapshot of your data structure:)

[#5]

Hi,

Please find below some screenshots.

The report looks like this:

The sort order is set:

As written in a post above, the sorting doesn't work correctly. If I add the field "man_sort_order_qv" to the dimensions, sorting suddenly works:

And this works even though I didn't add a sort order for the new dimension:

The sort order for the dimension "Description" is still set as visible in picture above...

Any idea?

[giakoum]

Can we get some sample report?

What if you omit the min function from the sorting expression?

[#7]

what it the first dimension (which has no label)

[jonathandienst · Accepted solution]

Hi

One way to solve this problem is to load Description as a dual. A dual has a text representation (in this case the description itself) and a numeric representation (in this case the sort order). You would load it with something like:

LOAD

     ....

     Dual(Description, man_sort_order_qv) As Description,

     ....

In the table sort dialog, check the Numeric Value check box for Description.

Hope that helps

Jonathan

PS - as description is now considered by QV to be a numeric field, it will right align it and will replace the text with #'s if it is too long to fit. You can use Text(Description) to override this behaviour.

[#9]

Hi Jonathan,

Thank you very much. This helped!

@all, thanks for your help as well.