Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pivot/Straight table show dimension on dimension

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.

1 Solution

Accepted Solutions
orital81
Partner - Creator III
Partner - Creator III

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)



View solution in original post

9 Replies
orital81
Partner - Creator III
Partner - Creator III

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.

Not applicable
Author

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.

orital81
Partner - Creator III
Partner - Creator III

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?

orital81
Partner - Creator III
Partner - Creator III

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?

Not applicable
Author

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.

orital81
Partner - Creator III
Partner - Creator III

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.

Not applicable
Author

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

orital81
Partner - Creator III
Partner - Creator III

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)



Not applicable
Author

very big 5mb?

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

Great work!