Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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!