Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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.
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.
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?
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?
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.
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.
I cannot see that it works 😕 The highest item with probility is not the first one.
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)
very big 5mb?
Anyways - It works, thanks alot! Didnt know there was a third parameter!
Great work!