topic Re: Pivot/Straight table show dimension on dimension in QlikView

Pivot/Straight table show dimension on dimension

Wed, 18 Sep 2013 07:30:55 GMT

Hello,

I have a problem which i simply does not know how to solve.

As it is now, when i select a item in my barchart, i have a straight table, which shows all related items (but only in one box) -

This means i cannot sort the values based on another column.

Then i made a pivot table, with an expression and hereby i can sort them by this field - BUT i dont want to show these values, and as we know, you cannot hide a column in a pivot table.

My question is then. How would you do this?

How would you show all related items sorted by an expression? but without showing the expression values...

I have attached a sample file.

Thanks in advance.

Re: Pivot/Straight table show dimension on dimension

orital81 — Wed, 18 Sep 2013 07:48:53 GMT

Hi Thomas

There is a way to hide a column in a pivot,

You can set the width to 0

set ch = ActiveDocument.GetSheetObject("CH##")

ch.SetPixWidth X,0

The X is the position of the column in the pivot.

Another option is to set a sort expression.

Re: Pivot/Straight table show dimension on dimension

Wed, 18 Sep 2013 07:52:03 GMT

I would like to use this as a last option, but thanks for the input. - Btw. Where do you type your expression?

And you cannot set a sort expression on an expression.

And as i told, i cannot show the items without an expression.

And if i show the dimension in the expression it would all be in one box.

Re: Pivot/Straight table show dimension on dimension

orital81 — Wed, 18 Sep 2013 08:02:01 GMT

I mean set a sort expression on a dimension.

Please clarify: when I open your attached model I see a chart and 3 tables

1. on which of the objects you would like to sort

2. What is the expression you want to sort by?

Re: Pivot/Straight table show dimension on dimension

orital81 — Wed, 18 Sep 2013 08:02:32 GMT

I mean set a sort expression on a dimension.

Please clarify: when I open your attached model I see a chart and 3 tables

1. on which of the objects you would like to sort

2. What is the expression you want to sort by?

Re: Pivot/Straight table show dimension on dimension

Wed, 18 Sep 2013 08:07:04 GMT

I want the pink straight table in the bottom to be sorted by sum(Probility) - But I dont think you can do so, cause all the items is included in one textbox.

Re: Pivot/Straight table show dimension on dimension

orital81 — Wed, 18 Sep 2013 08:34:38 GMT

There is a third parameter to Concat, it's the sort order.

Change your expression to this:

=concat(DimItemRecommendation.RecommendedConcatItem, Chr(10),Aggr(Sum(Probility),DimItemRecommendation.RecommendedConcatItem))

I tried it, it seem to work.

Re: Pivot/Straight table show dimension on dimension

Wed, 18 Sep 2013 08:43:59 GMT

I cannot see that it works 😕 The highest item with probility is not the first one.

Re: Pivot/Straight table show dimension on dimension

orital81 — Wed, 18 Sep 2013 08:56:27 GMT

It's sorting low to high.. try multiply by -1

So it will look like this:

concat(DimItemRecommendation.RecommendedConcatItem, Chr(10),-1*Aggr(Sum(Probility),DimItemRecommendation.RecommendedConcatItem))

It worked for me (the file is very big so I didn't attach it)

Re: Pivot/Straight table show dimension on dimension

Wed, 18 Sep 2013 09:21:35 GMT

very big 5mb?

Anyways - It works, thanks alot! Didnt know there was a third parameter!

Great work!