topic Re: Using an ID as Sort Expression on Pivot Table in QlikView

Using an ID as Sort Expression on Pivot Table

Thu, 26 Jul 2012 12:12:26 GMT

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

Re: Using an ID as Sort Expression on Pivot Table

Thu, 26 Jul 2012 12:18:40 GMT

you should sort ID by expression and paste there

=min(KPI-ID)

Re: Using an ID as Sort Expression on Pivot Table

Thu, 26 Jul 2012 12:39:19 GMT

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

Re: Using an ID as Sort Expression on Pivot Table

Thu, 26 Jul 2012 12:54:22 GMT

send snapshot of your data structure:)

Re: Using an ID as Sort Expression on Pivot Table

Fri, 27 Jul 2012 08:54:22 GMT

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?

Re: Using an ID as Sort Expression on Pivot Table

giakoum — Fri, 27 Jul 2012 09:01:13 GMT

Can we get some sample report?

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

Re: Using an ID as Sort Expression on Pivot Table

Fri, 27 Jul 2012 09:13:19 GMT

what it the first dimension (which has no label)

Re: Using an ID as Sort Expression on Pivot Table

jonathandienst — Fri, 27 Jul 2012 09:15:17 GMT

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.

Re: Using an ID as Sort Expression on Pivot Table

Thu, 09 Aug 2012 14:04:53 GMT

Hi Jonathan,

Thank you very much. This helped!

@all, thanks for your help as well.