Need help with a new list
I am working with a pre-existing model that I cannot change. In the model there are 2 dimensions, in this case we'll call them Number and Letter. what I want to do is make a new list where letters B and C now get their own Number. I assume this is done using a list, but I'm not sure how to go about doing it. Guidance?
7 replies
-
Hi
I'm sorry, but I don't understand what it is you are trying to achieve.
Can you explain in a different way?
Thanks,
Ian
-
Sure, I'll try again.
As is stands there is a model that I'm working with (Which I cannot change) that has 2 dimensions layed out as shown in the attached image.
What I want to do is create a custom diemension (ie. Number_NEW) where the Letter dimension data is reorganized (Which can also be used as a slicer in Present).
-
-
Hi
You'll need to create a Custom Column to do this, essentially building a new "Number" dimension that maps to the letters as you have described them.
Open a new Formulate calculation and select "Custom Column", then your data source:
Here's a script based on your data that maps the letters to a new Numbers dimension:
Case(
[Sheet1].[Letter]="A","1",
[Sheet1].[Letter]="B","7",
[Sheet1].[Letter]="D","2",
[Sheet1].[Letter]="E","2",
[Sheet1].[Letter]="C","8",
[Sheet1].[Letter]="F","3",
[Sheet1].[Letter]="G","4",
[Sheet1].[Letter]="H","4",
[Sheet1].[Letter]="I","5",
[Sheet1].[Letter]="J","6",
[Sheet1].[Letter]="K","6",
"N/A")Then use the New Numbers dimension in your grid / slicer:
Hope that helps.
Ian
-
Is there a way to do this without using script, but rather using the Tool-Panel?
-
Not much difference really.
Ian
-
-
I would suggest this optimization, building on Ian's formula:
Case([Sheet1].[Letter]="B",7,[Sheet1].[Letter]="C",8,[Sheet1].[number])so if its "B", set to 7, if its "C" set to 8, otherwise default to the original number column value
-
Fair enough!
-
