Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good morning,
I've got a pivot table where I am trying to combine a calculated dimension with some standard field dimensions. In one of the calculated dimensions I want to pick out the value of a field (field = Value) where another field is a specific value (field = Metric, such that Metric="Price"). I also have the field Metric as another standard dimension (the only one pivoted to be column headings) and then my only expression is the field Value. So basically I want to show, for the companies selected (Ticker/Issuer define the companies), the values of the metrics selected - but I also want to show the price as a dimension alongside each company. Please see attached example as I know that's probably hard to imagine without seeing it! The data is sensitive so I've had to scramble it, hope it all still makes sense though structurally.
I am having a problem getting my calculated dimension to work. It's the one labelled "PRICE" in the tob pivot table. NB I've got my calculated dimension set to "concat" for now, but it should eventually work as "only" - I'm just using concat to investigate what's happening.
I think the answer should be this (formula for the PRICE calculated dimension):
=concat(
{$<Metric={"Price"}>} Total <Ticker,Issuer> DISTINCT Value
,';')
... but if you test that you see it gives // Error in calculated dimension.
The closest I can get is a concatenation of the values for all companies, by excluding the <Ticker,Issuer> bit. This is what I've left it as in the table for you to see. However, I obviously don't want the concatenation (concat is just a test to see what's happening), I want it to pick out the price for the company of the row it's on.
I can get the right results using it as an expression (see lower table) but then obviously the dimension Metric doen't work properly (the layout should be as per the top table). But you can see the numbers I'm aiming to get.
The complication, I think, is because really Price should be a separate field. However, the data structure and sources are very complicated for this. I have Price as a field in a new "add on" datasource but I'm trying to avoid the need to use this extra datasource, as Price is already in the main data as a value of the field Value with Metric="Price".
Any suggestions would be greatly appreciated!
Thank you very much. 
Sarah
